NOT EXISTS frente a NOT IN: la trampa de NULL y los anti-joins

Puedes encontrar filas sin coincidencia en otra tabla de tres formas: NOT IN, NOT EXISTS y LEFT JOIN ... IS NULL. Parecen intercambiables, pero NOT IN esconde una trampa de NULL que un buen dia vacia tu resultado sin avisar. Vamos a compararlas sobre un esquema users, orders, employees.

El problema: usuarios sin pedidos

El anti-join clasico: quien nunca hizo un pedido. La version intuitiva con NOT IN:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Mientras orders.user_id no tenga NULL, todo bien. Pero user_id puede admitir nulos (compra de invitado, borrado logico), y entonces la consulta se rompe: sin error, sin aviso.

La trampa de NULL en NOT IN

NOT IN (...) se expande a una cadena id <> v1 AND id <> v2 AND .... Si algun valor es NULL, la comparacion id <> NULL no es ni TRUE ni FALSE, sino UNKNOWN. En la logica de tres valores TRUE AND UNKNOWN = UNKNOWN, asi que la fila se descarta. Resultado: un solo NULL en la subconsulta colapsa todo el resultado a vacio.

-- orders tiene una fila con user_id IS NULL.
-- Esto devuelve NADA, aunque haya usuarios sin pedidos:
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

• Es el comportamiento del estandar SQL, no un fallo de un motor concreto.
• NULL IN (...) y NULL NOT IN (...) tampoco dan nunca TRUE.
• PostgreSQL, MySQL y ClickHouse se comportan igual.

El parche es filtrar el NULL: WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL). Funciona, pero es facil olvidarlo, y mas aun en una columna dentro de una clave compuesta.

NOT EXISTS: seguro por defecto

NOT EXISTS comprueba si existe al menos una fila y usa logica de existencia de dos valores, asi que NULL no lo descarrila:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Una fila con o.user_id IS NULL simplemente no cumple o.user_id = u.id y se omite en silencio: nada de colapso. El SELECT 1 interno es un idiom: la lista del select en EXISTS no se evalua, solo importa que exista la fila.

Las subconsultas correlacionadas tambien encajan bien con condiciones de varias columnas:

-- Empleados sin subordinados (nadie los pone como manager_id):
SELECT e.id, e.name, e.dept
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees s
    WHERE s.manager_id = e.id
);

LEFT JOIN ... IS NULL

La tercera opcion es un join externo filtrado por la no coincidencia. Tambien es seguro ante NULL como condicion de anti-join:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

• Comprueba IS NULL sobre la propia clave del join (o.user_id), no sobre cualquier columna nullable de orders.
• En una relacion uno a muchos el join multiplica filas, asi que luego sueles necesitar DISTINCT o GROUP BY: trabajo extra. NOT EXISTS evita eso por completo.

Rendimiento y planes anti-join

PostgreSQL moderno ejecuta tanto NOT EXISTS como LEFT JOIN ... IS NULL como un verdadero anti join (Hash Anti Join / Merge Anti Join en EXPLAIN), parando en la primera coincidencia. Con una columna nullable, el planificador no puede convertir NOT IN en anti-join por la semantica de NULL, y suele salir un plan mas pesado.

• En PostgreSQL prefiere NOT EXISTS por seguridad ante NULL y por velocidad.
• MySQL 8 tambien optimiza bien NOT EXISTS; historicamente NOT IN podia ser bastante mas lento.
• ClickHouse ofrece un LEFT ANTI JOIN limpio: un anti-join explicito sin subconsulta.
• Apoya el predicado correlacionado de NOT EXISTS con un indice (por ejemplo en orders(user_id)).

Conclusion: haz de NOT EXISTS tu opcion por defecto para "filas sin coincidencia". Reserva NOT IN para listas estaticas sin NULL (status NOT IN ('paid','shipped')) y usa LEFT JOIN ... IS NULL cuando ademas necesites columnas del lado sin coincidencia.