Take care when using Not In operation in your query.
Example:
select * from policy m
where m.policy_number is not null
and m.ref_code not in (select ref_code from staging)
Normaly the query return some records but one day you see the results return null. The logic is very clear but you don’t know why the value is wrong.
The strange behavior here is the sub query contains NULL value.
When the subquery returns even one null, NOT IN will not match any rows.
Let’s say, for illustration purposes that there are 4 rows in the table staging, there’s a column called ID with values 1..4
WHERE ref_code NOT IN (SELECT ref_code FROM staging)
is equivalent to:
WHERE ref_code != (SELECT ref_code FROM staging WHERE ID=1)
AND ref_code != (SELECT ref_code FROM staging WHERE ID=2)
AND ref_code != (SELECT ref_code FROM staging WHERE ID=3)
AND ref_code != (SELECT ref_code FROM staging WHERE ID=4)
Let’s further say that staging is NULL where ID = 4. Hence that != comparison returns UNKNOWN.The logical truth table for AND states that UNKNOWN and TRUE is UNKNOWN, UNKNOWN and FALSE is FALSE. There is no value that can be AND’d with UNKNOWN to produce the result TRUE.
Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned.
You should use not exist
select * from policy m
where m.policy_number is not null
and not exists (select ref_code from staging);
or check null value in the sub query to have the correct results.
select * from policy m
where m.policy_number is not null
and m.ref_code not in (select ref_code from staging and ref_code is not null);