How NOT IN works with NULL values

Recently I faced one issue with NULL value with NOT IN, I have added the details here about what I learned.

Suppose we have two table Category and Product

Category

id | name 
---------------- 
1  | Category 1 
2  | Category 2 
3  | Category 3 
4  | Category 4

Product

id | name       | category_id 
------------------------------
1  | Product 1  | 1 
2  | Product 2  | 2 
3  | Product 3  | 2 
4  | Product 4  | 3 
5  | Product 5  | 4 
6  | Product 6  | NULL

Scenario :- *I just want to retrieve all the Products excluding Category Category 1 and Category 3
so I wrote following query :-

SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id" WHERE "categories.name" NOT IN ("Category 1", "Category 3")

it gives following result.

id | name      | category_id 
---------------------------- 
2  | Product 2 | 2 
3  | Product 3 | 2 
5  | Product 5 | 4

It excludes NULL values as well with Category Category 1 and Category 3

I was curious to know why NULL values are excluded, so I tried excluding NULL value as well.

SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id" WHERE "categories.name" NOT IN ("Category 1", "Category 3", NULL)

A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values.

Why this happens :-

The reason for the difference in behavior is down to the three valued logic(three valued logic) used in SQL. Predicates can evaluate to True, False, or Unknown.

How to overcome this :- * You can explicitly include them using IS NULL as below.

SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id" WHERE "categories.name" NOT IN ("Category 1", "Category 3") OR "categories.name" IS NULL

OR * you can use NOT EXISTS

SELECT "products".* FROM "products" WHERE (NOT EXISTS (SELECT "categories".* FROM "categories" WHERE "categories.name" = "Category 1" OR "categories.name" = "Category 3"))

which will return expected result

id | name       | category_id 
------------------------------
2  | Product 2  | 2 
3  | Product 3  | 2 
5  | Product 5  | 4 
6  | Product 6  | NULL

Hope this will help! Feel free to share your thoughts, I’d love to hear those!

Reference :-

SQL NOT IN()

Originally published at https://blog.kiprosh.com on June 1, 2016.