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 <> ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial