Anti-joins en SQL: encontrar filas sin coincidencia

Un anti-join responde a una sola pregunta: «¿qué filas de la tabla A no tienen coincidencia en la tabla B?» Usuarios sin pedidos, pedidos sin pago, empleados sin responsable. SQL no tiene una palabra clave ANTI JOIN, así que lo expresas de una de tres formas clásicas. Las tres devuelven la respuesta «correcta» con datos limpios, pero divergen drásticamente en cuanto entra NULL en juego. Vamos a recorrerlas sobre un mismo esquema y a averiguar a cuál conviene recurrir.

Usaremos estas tablas (PostgreSQL):

CREATE TABLE users (
  id    bigint PRIMARY KEY,
  email text NOT NULL
);

CREATE TABLE orders (
  id       bigint PRIMARY KEY,
  user_id  bigint,          -- nullable: a "guest" order
  amount   numeric NOT NULL
);

Tarea en curso: encontrar los usuarios que no han realizado ningún pedido.

LEFT JOIN ... WHERE b.id IS NULL

El enfoque más visual. Haz un LEFT JOIN que conserve cada fila de users y luego descarta las que encontraron coincidencia.

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;   -- no match => the whole right side is NULL

Cómo leerlo: el LEFT JOIN rellena con NULL cada columna de orders cuando no hay coincidencia. El filtro WHERE o.id IS NULL conserva exactamente esas filas que no coincidieron. El detalle clave: comprueba IS NULL sobre una columna que nunca sea NULL en una fila real; la clave primaria o.id es perfecta. Si compruebas o.user_id (que aquí admite nulos), podrías capturar por accidente pedidos de invitados y obtener basura.

• A favor: intuitivo, y resulta fácil incorporar columnas extra de orders para depurar.
• En contra: la semántica de «elimina lo que coincidió» no resulta obvia para quien lea la consulta después.
• Trampa: la coincidencia por varias columnas vuelve la condición IS NULL verbosa y frágil.

NOT EXISTS — la forma recomendada

NOT EXISTS con una subconsulta correlacionada expresa la intención de forma literal: «no existe ningún pedido para este usuario».

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

Por qué suele ser la mejor opción:

• Se lee como el requisito. «No existe tal pedido.» Listo.
• Seguro frente a NULL. EXISTS solo comprueba si existen filas; no compara valores de vuelta con la consulta externa. Un NULL en o.user_id simplemente no satisface o.user_id = u.id y cuenta como ausencia de coincidencia, sin sorpresas.
• Rendimiento. El planificador de PostgreSQL ejecuta tanto NOT EXISTS como LEFT JOIN / IS NULL como el mismo anti join físico (verás Hash Anti Join en EXPLAIN). En la práctica son equivalentes en velocidad; NOT EXISTS se adelanta con claves compuestas.

Una clave compuesta se mantiene limpia, sin comprobaciones IS NULL dispersas:

WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.region = u.region
)

NOT IN y la trampa de NULL

NOT IN parece compacto y tentador, y es el más traicionero de los tres.

-- DANGEROUS if orders.user_id contains NULL
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Si la subconsulta produce aunque sea un único NULL (¡y aquí user_id admite nulos por los pedidos de invitados!), la consulta devuelve cero filas. No es un fallo de la base de datos; es la lógica trivaluada de SQL. x NOT IN (1, 2, NULL) se expande a x <> 1 AND x <> 2 AND x <> NULL. Esa última comparación se evalúa como UNKNOWN, de modo que la expresión completa nunca puede llegar a ser TRUE y la fila se descarta en silencio.

• Trampa: el fallo es silencioso. La consulta no da error; simplemente devuelve un resultado vacío (o truncado).
• Si tienes la certeza de que no hay NULL, añade un filtro explícito; pero es fácil olvidarlo cuando los datos cambian:

WHERE u.id NOT IN (
  SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

Notas entre motores:

• MySQL se comporta de forma idéntica: la misma lógica trivaluada, el mismo estallido con NULL.
• ClickHouse también se ve afectado; además, su JOIN tiene sus propias rarezas, así que NOT IN sobre una columna garantizada sin NULL (o LEFT ANTI JOIN) suele resultar más limpio. Cabe destacar que ClickHouse es uno de los pocos motores con una sintaxis explícita LEFT ANTI JOIN.

Cuál elegir

La regla breve:

• Usa NOT EXISTS por defecto. Expresivo, seguro frente a NULL, y el optimizador lo convierte en un anti join. Que sea tu opción habitual.
• LEFT JOIN ... IS NULL — cuando además quieras columnas de la tabla de la derecha, o simplemente te resulte más legible. Comprueba siempre IS NULL sobre una clave que no admita nulos.
• NOT IN — solo cuando la columna de la subconsulta tenga garantizado ser NOT NULL (por ejemplo, una clave primaria). De lo contrario, filtra los NULL explícitamente, o sencillamente no lo uses.

Lista de comprobación final: ¿puede la subconsulta devolver NULL? Si es así, nada de NOT IN. ¿Necesitas campos del lado derecho? Usa LEFT JOIN. En cualquier otro caso, escribe NOT EXISTS y olvídate de que existe la lógica trivaluada.