Piège
NOT IN avec un NULL
Un seul NULL dans la liste et
NOT IN ne renvoie plus aucune ligne.x NOT IN (1, NULL) vaut x <> 1 AND x <> NULL, et x <> NULL est inconnu, jamais vrai. Utilisez
NOT EXISTS, ou ajoutez WHERE col IS NOT NULL à la sous-requête.
Le piège
WITH excluded(city) AS (VALUES ('Paris'), (NULL))
SELECT COUNT(*) AS customers
FROM customers
WHERE city NOT IN (SELECT city FROM excluded);
| customers |
|---|
| 0 |
La correction
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 |
Pratiquer :NOT IN et les lignes disparues