Pitfall
NOT IN with a NULL
One NULL in the list and
NOT IN returns no rows at all.x NOT IN (1, NULL) is x <> 1 AND x <> NULL, and x <> NULL is unknown, never true. Use
NOT EXISTS, or add WHERE col IS NOT NULL to the subquery.
The trap
WITH excluded(city) AS (VALUES ('Paris'), (NULL))
SELECT COUNT(*) AS customers
FROM customers
WHERE city NOT IN (SELECT city FROM excluded);
| customers |
|---|
| 0 |
The fix
WITH excluded(city) AS (VALUES ('Paris'), (NULL))
SELECT COUNT(*) AS customers
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM excluded e WHERE e.city = c.city);
| customers |
|---|
| 348 |
Practise:NOT IN and the missing rows