Trampa
NOT IN con un NULL
Basta un NULL en la lista para que
NOT IN no devuelva ninguna fila.x NOT IN (1, NULL) equivale a x <> 1 AND x <> NULL, y x <> NULL es desconocido, nunca verdadero. Use
NOT EXISTS, o añada WHERE col IS NOT NULL a la subconsulta.
La trampa
WITH excluded(city) AS (VALUES ('Paris'), (NULL))
SELECT COUNT(*) AS customers
FROM customers
WHERE city NOT IN (SELECT city FROM excluded);
| customers |
|---|
| 0 |
La corrección
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 |
Practicar:NOT IN and the missing rows