Joe Celko's SQL for Smarties, 3rd Edition

14.3. NULLs and the IN() Predicate

NULLs make some special problems in a NOT IN() predicate with a subquery. Consider these two tables:

CREATE TABLE Table1 (x INTEGER);
INSERT INTO Table1 VALUES (1), (2), (3), (4);

CREATE TABLE Table2 (x INTEGER);
INSERT INTO Table2 VALUES (1), (NULL), (2);

Now execute the query:

SELECT *
 FROM Table1
 WHERE x NOT IN (SELECT x FROM Table2)

Let’s work it out step by painful step:

1.	Do the subquery: SELECT * FROM Table1 WHERE x NOT IN (1, NULL, 2);
2.	Convert the NOT IN() to its definition: SELECT * FROM Table1 WHERE NOT (x IN (1, NULL, 2));
3.	Expand IN() predicate: SELECT * FROM Table1 WHERE NOT ((x = 1) OR (x = NULL) OR (x = 2));
4.	Apply DeMorgan’s law: SELECT * FROM Table1 WHERE ((x <> 1) AND (x <> NULL) AND (x <> ...