Today I was working on a SQL query, and it took me a while before I figured out an issue.
A query goes something like this – SELECT * FROM Table t1 where t1.UserId NOT IN ( SELECT UserId FROM Table t2 WHERE t2.Status = 1) WHERE Status = 2
Lets assume Status 1 = Active and Status 2 = Terminated and UserId can be NULL. There are few records in Table with UserIds Null for Status = 1. When i ran the query I was expecting few records to output but for some reason there are 0 records. After looking at the query carefully, i noticed that the inner query was returning NULL values and that ends being as below. It return NULL
SELECT * FROM Table t1 where t1.UserId NOT IN ( 1, 2, NULL) — Return 0 records although there are matching records with 1, 2
NULL can be dangerous enough to mess up the whole query and result set. So better handle NULL values properly.