NOT IN clause not working when dealing with NULLs!

A very common issue that I see on my experience is that  “NOT IN” clause not working.

Here is a quick repro:

I create a PRODUCTS table with PRODUCT_ID and PRODUCT_NAME columns. I insert three records in it.

CREATE COLUMN TABLE PRODUCTS (PRODUCT_ID INT, PRODUCT_NAME NVARCHAR (50));

INSERT INTO PRODUCTS VALUES (101,’P1′);

INSERT INTO PRODUCTS VALUES (102,’P2′);

INSERT INTO PRODUCTS (PRODUCT_ID) VALUES (103);   

Note that a NULL gets inserted in the third record for PRODUCT_NAME columns. This is intentional.

This is what the PRODUCTS table looks like, note the NULL there

Now my final query: Give me all PRODUCT_ID, PRODUCT_NAME the from PRODUCT table that do not exist these records ‘P1’, ‘P2’

SELECT PRODUCT_ID, PRODUCT_NAME

     FROM PRODUCTS

     WHERE PRODUCT_NAME NOT IN (‘P1’, ‘P2’)                                                    

And I get an empty result set.

The technical issue is:

When the comparison is done with NULL, it returns UNKNOWN because NULL is an unknown value and anything compared with unknown can only result in unknown. And UNKNOWN in this case of Logical comparison is treated as FALSE and an empty result set is being returned.

Solution: Put an additional filter.

SELECT PRODUCT_ID, PRODUCT_NAME FROM PRODUCTS

WHERE (PRODUCT_NAME NOT IN (‘P1’, ‘P2’) OR PRODUCT_NAME IS NULL);  

Hope it helps to every one.

Best Regards

Srinivas