SQL: NOT IN NULL

Posted: 2020-11-09

Got caught out today by a reasonably common issue which can be a bit perplexing.

With a fairly simple query like:

SELECT col_a
FROM table_1
WHERE col_a NOT IN (SELECT col_a FROM table_2)

This can unexpectedly return zero rows if SELECT col_a FROM table_2 returns a NULL, so the obvious way to fix it is to replace the WHERE condition with:

WHERE col_a NOT IN (SELECT col_a FROM table_2)

or to use the less well know (at least by me) EXISTS operator instead:

SELECT col_a
FROM table_1 t1
WHERE col_a NOT EXISTS (SELECT col_a FROM table_2 t2 WHERE t1.col_a = t2.col_a)

Useful Stack overflow post explaining more about why this issue occurs