Trappola
NOT IN con un NULL
Basta un NULL nella lista e
NOT IN non restituisce alcuna riga.x NOT IN (1, NULL) equivale a x <> 1 AND x <> NULL, e x <> NULL è sconosciuto, mai vero. Usa
NOT EXISTS, oppure aggiungi WHERE col IS NOT NULL alla subquery.
La trappola
WITH excluded(city) AS (VALUES ('Paris'), (NULL))
SELECT COUNT(*) AS customers
FROM customers
WHERE city NOT IN (SELECT city FROM excluded);
| customers |
|---|
| 0 |
La correzione
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 |
Esercitati:NOT IN and the missing rows