This may sound
a little silly for SQL experts, but can really help for someone working newly
on SQL queries. The NOT IN query is used when there is a need to select the
data on the first table, but not the matching records on the second. While
selecting such data, there are chances of getting just the NULL in the output
though there should be records for display.
Let’s now
take a simple example as below for a demonstration:
select ID from Table1
where ID not in (select ID from table2)
From the above query, we have an empty result. Most of the time there is confusion when there
are millions of records and the NULLS are left unnoticed. Having NULLs in the
columns for comparison can lead the results to be NULL.
Let’s try
the same query by just adding IS NOT NULL for table2 in the WHERE clause.
select ID from Table1
where ID not in (select ID from table2 where ID is not null)
Here, we
can see that the results are coming up for us. There are chances that we may
forget or miss that thought that NULLs are present in either of the tables. So,
always use the IS NOT NULL statement to fix the confusion of NOT IN not
working.
0 comments:
Post a Comment