NOT IN, NOT EXISTS y NULL: errores de anti-join en SQL Server

Un informe busca clientes que nunca hicieron pedidos. Funciona durante meses y de repente no devuelve nada. Todavía existen clientes inactivos y la consulta no cambió. Basta un pedido importado sin referencia de cliente para revelar el problema si se utiliza NOT IN.

SQL evalúa comparaciones con NULL como desconocidas, además de verdadero y falso. WHERE conserva únicamente las filas cuya condición es verdadera. No conserva desconocido. Entender esta regla resulta más útil que memorizar una recomendación universal contra IN.

Reproducir el resultado inesperado

La primera consulta no devuelve clientes. NOT EXISTS devuelve 2 y 3. El left join devuelve también 2 y 3 porque comprueba una columna que no puede ser NULL en un pedido real. El último resultado cuenta tres pedidos, pero solamente dos referencias de cliente.

DECLARE @Customers TABLE(CustomerId int NOT NULL PRIMARY KEY);
DECLARE @Orders TABLE(OrderId int NOT NULL, CustomerId int NULL);
INSERT @Customers VALUES (1),(2),(3);
INSERT @Orders VALUES (10,1),(11,NULL),(12,1);

SELECT c.CustomerId
FROM @Customers AS c
WHERE c.CustomerId NOT IN
    (SELECT o.CustomerId FROM @Orders AS o);

SELECT c.CustomerId
FROM @Customers AS c
WHERE NOT EXISTS
(
    SELECT 1 FROM @Orders AS o
    WHERE o.CustomerId=c.CustomerId
);

SELECT c.CustomerId
FROM @Customers AS c
LEFT JOIN @Orders AS o ON o.CustomerId=c.CustomerId
WHERE o.OrderId IS NULL;

SELECT COUNT(*) AS AllOrders,
       COUNT(CustomerId) AS OrdersWithCustomer
FROM @Orders;

Para el cliente 2, NOT IN requiere que se cumpla la desigualdad frente a todos los valores de la subconsulta. La comparación con 1 es verdadera; con NULL resulta desconocida. La condición completa no es verdadera. El cliente 1 queda excluido por su coincidencia real y no queda ninguna fila.

Eliminar NULL de la subconsulta puede corregir NOT IN para esta clave exterior no nullable. Es una reparación válida si la nulabilidad forma parte de un contrato documentado. Si posteriormente la expresión exterior permite NULL, hay que revisar otra vez su significado. NOT EXISTS expresa más directamente la ausencia de una coincidencia.

Elegir la relación correcta

NOT EXISTS pregunta si existe alguna fila correspondiente. Varios pedidos del cliente 1 no duplican al cliente 2. Sin embargo, no convierte NULL en igual a NULL. Si el negocio considera que dos valores ausentes coinciden, la igualdad ordinaria dentro de la subconsulta no implementa esa regla.

El left join necesita una columna derecha que identifique inequívocamente la ausencia de una fila. Una fecha de entrega nullable puede estar vacía en un pedido existente. OrderId se declara NOT NULL en la muestra; su NULL después del join demuestra que no hubo coincidencia.

La ubicación de los filtros también cambia el significado. Para clientes sin pedidos pagados, coloca el estado de pago dentro de NOT EXISTS o en ON del left join. Filtrar ese estado derecho en el WHERE exterior puede eliminar las filas extendidas con NULL que querías encontrar. Construye una muestra con pedidos pagados, impagados y ausentes.

Comprobar semántica antes del rendimiento

En una tabla grande, un índice que comience por Orders.CustomerId puede atender la comprobación de existencia. Si interviene el pago, considera distribución e índice filtrado o compuesto. El optimizador puede convertir varias formas SQL en operadores anti-semi-join similares. La sintaxis por sí sola no demuestra velocidad.

EXCEPT utiliza semántica de conjuntos, elimina duplicados y trata los NULL como iguales para comparar valores distintos. No es un reemplazo transparente cuando multiplicidad o coincidencias nulas forman parte del contrato. Añadir DISTINCT después de una unión muchos a muchos accidental puede esconder un error y añadir procesamiento.

La matriz de pruebas debe incluir lado derecho vacío, una coincidencia, coincidencias duplicadas, NULL a la derecha y una clave izquierda nullable si el esquema la admite. Comprueba filas concretas, no únicamente cantidades. Dos resultados incorrectos pueden tener el mismo tamaño.

Si el error aparece después de una importación, investiga también por qué se aceptó la referencia ausente. Corregir el informe no completa los datos originales. Un anti-join fiable expresa la ausencia que importa al negocio, conserva las reglas de NULL y solo después optimiza el acceso mediante índices.

Referencias técnicas: Microsoft Learn: IN · Microsoft Learn: EXISTS.