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

id | name
--------
1  | CAT1
2  | CAT2
3  | CAT3
4  | CAT4

Product

id | name | category_id

1 | PR1 | 1
2 | PR2 | 2
3 | PR3 | 2
4 | PR4 | 3
5 | PR5 | 4
6 | PR6 | NULL

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

Product.joins(:category).where("categories.name NOT IN (?)", ['CAT1', 'CAT3'])

it gives following result.

id | name | category_id
-------------------
2  | PR2  | 2
3  | PR3  | 2
5  | PR5  | 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 (?)", ['Cat1', 'Cat2', nil])

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

Why this happen :-

The reason for the difference in behavior is down to the [three valued logic(three valued logic)][1] 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 product.category_id IS NULL", [1, 3])

OR

  • you can use NOT EXISTS
 Product.where("NOT EXISTS (" + Category.where("(name = 'Cat1' OR name = 'Cat2') 
      AND products.category_id = categories.id ").to_sql + ")")
id | name | category\_id
----------------------
2  | PR2  | 2
3  | PR3  | 2
5  | PR5  | 4
6  | PR6  | NULL

Reference :-

[SQL NOT IN()][2]
[1]: https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/
[2]: http://stackoverflow.com/questions/5231712/sql-not-in-not-working?answertab=oldest#tab-top