SQL: cuidado con NULL en WHERE NOT IN

Si alguna vez has escrito algo de SQL, probablemente conozcas el estatus especial de NULL en SQL.

Para refrescarte la memoria: NULL indica ausencia de un valor. No puedes utilizar NULL en una comparación, siempre dará como resultado NULL. Si esto no conduce al resultado deseado, existen operadores especiales para tratar NULL explícitamente. Se comporta de forma similar a NaN en las operaciones con coma flotante.

Código de ejemplo:

[sourcecode language="sql"]
CREATE TABLE pony
(
id INT PRIMARY KEY,
name VARCHAR(255)
);

INSERT INTO pony (id, name)
VALUES
(1, "Twilight Sparkle"),
(2, "Rainbow Dash"),
(3, "Pinkie Pie"),
(4, "Rarity"),
(5, "Applejack");

SELECT * FROM pony;
-- 5 filas como se esperaba

SELECT * FROM pony
WHERE id = NULL;
-- 0 filas como se esperaba

SELECT * FROM pony
WHERE id != NULL;
-- 0 filas, leve wtf

SELECT * FROM pony
WHERE id IS NOT NULL;
-- 5 filas como se esperaba
[/sourcecode]

NULL sigue funcionando intuitivamente cuando se utiliza WHERE IN:

[sourcecode language="sql"]
SELECT * FROM pony
WHERE id IN (1, 2, NULL);
-- 2 filas como se esperaba

-- sentencia equivalente:
SELECT * FROM pony
WHERE id = 1
OR id = 2
OR id = null;
[/sourcecode]

DONDE NO ENTRA es donde las cosas se complican:

[sourcecode language="sql"]
SELECT * FROM pony
WHERE id NOT IN (1, 2, NULL);
-- 0 filas, wtf mayor
[/sourcecode]

Tiene sentido si divides la cláusula en comparaciones individuales:

[sourcecode language="sql"]
SELECT * FROM pony
WHERE NOT (
id = 1
OR id = 2
OR id = NULL
);
[/sourcecode]

Y luego elimina el paréntesis utilizando las leyes de De Morgan:

[sourcecode language="sql"]
SELECT * FROM pony
WHERE id != 1
AND id != 2
AND id != NULL;
[/sourcecode]

Como se explica en la introducción, id != NULL es siempre NULL, por lo que toda la cláusula WHERE es siempre FALSE.

Consejos prácticos y el problema de la subconsulta

Si utilizas datos externos, por ejemplo de XML o un CSV, debes filtrar los valores vacíos.

Más insidiosamente NULO puede colarse a través de una subconsulta. Supongamos que queremos saber cuántos ponis están actualmente fuera de la ciudad:

[sourcecode language="sql"]
WITH ponies_in_town AS (
SELECT pony.id, pony.name
FROM pueblo
LEFT JOIN pueblo_pony ON pueblo
WHERE pueblo.name = 'Ponyville'
)
SELECT
COUNT(*) AS num_ponies_out_of_town
FROM pony
WHERE id NOT IN (
SELECT id FROM ponies_in_town
)
[/sourcecode]

Si Applejack está sola en el pueblo, dice que se han ido 4 ponis. Si Applejack también se va, dice que se han ido 0 ponis. La solución a este problema se deja como ejercicio para el lector.

Las subconsultas son buenas

Permíteme hacer hincapié en esto. La alternativa a una subconsulta es colocar uniones de tablas en la consulta principal. Esto puede dar lugar a un problema insidioso más sutil, que es la duplicación de filas. El SQL que utiliza subconsultas es más "modular" y, por tanto, más fácil de entender y reutilizar. También puede ser más rápido.

El ejemplo anterior, tal como está, no lo demuestra, pero si añadimos algunos requisitos más podremos demostrar estas ventajas. Así que sigue utilizando WHERE NOT IN, pero ten en cuenta NULL.

Pero, ¿y si en realidad quiero tener en cuenta los NULLS?

Haz una cláusula where separada para null: WHERE val NOT IN (1, 2) OR val IS NULL

Autor Erik van Velzen | 02 mar 2018