SELECT *
FROM TABLE1
WHERE ID NOT IN (
SELECT ID
FROM TABLE2
)
When I ran the above query I know the condition will give me the results, because the data exists in the TABLE1
which is not in TABLE2
. But I didn’t go any results. I got surprised. After I a while I found out the issue.
So the reason is that ID
column in the TABLE2
have null values. If any value of ID
in the subquery is NULL
, then the entire NOT IN
clause fails to match anything. This is standard SQL behavior because NULL
makes the whole comparison unknown.
Solution:
- You can use
NOT NULL
condition in the sub-query as below:
SELECT *
FROM TABLE1
WHERE ID NOT IN (
SELECT ID
FROM TABLE2
WHERE ID IS NOT NULL
)
- Use
NOT EXISTS
:
SELECT *
FROM TABLE1 T1
WHERE NOT EXISTS (
SELECT 1
FROM TABLE2 T2
WHERE T1.ID = T2.ID
)
The NOT EXISTS
will automatically handle null values and safer to use.