NOT IN no devuelve filas cuando la subconsulta contiene NULL

WITH lista_exclusiones(cliente_id) AS (
  VALUES (1), (3), (NULL)
)
SELECT id, nombre
FROM clientes
WHERE id NOT IN (
  SELECT cliente_id
  FROM lista_exclusiones
)
ORDER BY id;

La causa: SQL usa lógica de tres valores

En SQL una comparación con NULL normalmente no produce verdadero ni falso, sino desconocido. Una forma útil de razonar sobre x NOT IN (1, 3, NULL) es imaginar una cadena de desigualdades conectadas con AND. Para un valor como 2, las comparaciones con 1 y 3 son verdaderas, pero 2 <> NULL es desconocida; el conjunto no puede demostrar que 2 sea distinto de todos los valores.

Corrección 1: elimina NULL del conjunto si no representa una exclusión válida

WITH lista_exclusiones(cliente_id) AS (
  VALUES (1), (3), (NULL)
)
SELECT id, nombre
FROM clientes
WHERE id NOT IN (
  SELECT cliente_id
  FROM lista_exclusiones
  WHERE cliente_id IS NOT NULL
)
ORDER BY id;

Esta solución es correcta cuando NULL en la fuente significa «no hay id de exclusión» y, por tanto, debe ignorarse.

Corrección 2: expresa la ausencia con NOT EXISTS

Si la intención real es «no existe una fila de exclusión que coincida con este cliente», NOT EXISTS expresa directamente esa pregunta. El NULL de otra fila no crea una comparación global contra todos los valores.

WITH lista_exclusiones(cliente_id) AS (
  VALUES (1), (3), (NULL)
)
SELECT c.id, c.nombre
FROM clientes AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM lista_exclusiones AS e
  WHERE e.cliente_id = c.id
)
ORDER BY c.id;

Con estos datos devuelve las mismas diez filas que la versión de NOT IN con IS NOT NULL. No elijas una forma por costumbre: elige la que represente con mayor claridad la regla del dato.

Método de diagnóstico

1. Ejecuta la subconsulta sola. Comprueba si devuelve NULL.
2. Cuenta nulos. Usa una comprobación como WHERE clave IS NULL para entender su origen.
3. Define la semántica. Decide si esos nulos deben ignorarse o si revelan un problema de modelado/datos.
4. Prueba la exclusión con pocos valores conocidos. Verifica una fila que debe entrar y otra que debe quedar fuera.
5. Solo entonces optimiza. Primero corrige la lógica; después estudia índices o planes si hace falta.

Cómo prevenir el problema

• Si una clave de exclusión no debería ser nula, protégela con una restricción adecuada en el modelo.
• Si NULL es válido, trata esa posibilidad de forma explícita en consultas de pertenencia.
• Cuando la pregunta sea de existencia por relación, considera EXISTS/NOT EXISTS.
• No sustituyas el diagnóstico por COALESCE a un valor inventado salvo que ese valor tenga una semántica real y no pueda colisionar con datos válidos.

Compatibilidad y matiz entre motores

PostgreSQL documenta que NOT IN devuelve NULL, no verdadero, cuando no hay coincidencia pero el conjunto derecho contiene al menos un NULL. SQLite publica la misma matriz de resultados para IN/NOT IN. La solución conceptual —comprender la lógica de NULL y expresar correctamente la ausencia— no depende de una extensión concreta.

Práctica correctiva

WITH lista_exclusiones(cliente_id) AS (
  VALUES (2), (6), (NULL)
)
SELECT c.id, c.nombre
FROM clientes AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM lista_exclusiones AS e
  WHERE e.cliente_id = c.id
)
ORDER BY c.id;

Conceptos relacionados

Fuentes técnicas consultadas

Las fuentes oficiales se usaron para verificar la semántica de NOT IN cuando interviene NULL.

• PostgreSQL: Subquery Expressions — NOT IN
• SQLite: IN and NOT IN operators