Beware using NULL in WHERE NOT IN

data-coding
idea
sql
spring

SELECT *
FROM pony  
WHERE id NOT IN (1, 2, NULL)
-- 0 rows, major wtf

It's because:

SELECT *
FROM pony  
WHERE NOT (  
  id = 1  
  OR id = 2  
  OR id = NULL  
)

And then remove the parenthesis using De Morgan’s laws:

SELECT *
FROM pony  
WHERE
  id != 1  
  AND id != 2  
  AND id != NULL

Like explained in the intro, id != NULL is always NULL, therefore the entire WHERE clause is always FALSE.

Advice: Keep using subqueries, mind NULL values. Still better than JOINs that can cause duplicates.

Metadata