Recently I faced one scenario with NULL value with NOT IN, I have added the details here about what I learnt.
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 1 and Category 3
so I wrote following query :-

Product.joins(:category).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_id 1 & 2

  • I was curious why NULL values are excluded, so I was tried excluding NULL value as well.
Product.joins(:category).where("categories.name NOT IN (?)", ['Category 1', 'Category 3', nil])

A NOT IN query will excludes the rows having a NULL value.

Why this happen :-

The reason for the difference in behavior is down to the 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.
    Product.where("products.category_id NOT IN (?) OR products.category_id IS NULL", [1, 3])

OR

  • you can use NOT EXISTS
    Product.where("NOT EXISTS (" + Category.where("(name = 'Category 1' OR name = 'Category 3') AND products.category_id = categories.id ").to_sql + ")")

O/P -

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

References :-
SQL NOT IN