Respuesta.

Historia de la pregunta

A menudo se requiere formular consultas en las que los criterios de filtrado se definen dinámicamente: la lista de valores la establece el usuario de la interfaz, y los valores omitidos (NULL) también deben poder incluirse o no en el resultado. Tal consulta debe funcionar rápidamente con grandes volúmenes y ser correcta desde el punto de vista semántico de SQL.

Problema

La combinación incorrecta de las condiciones IN, NOT IN y la verificación de NULL lleva a resultados inesperados porque en SQL la expresión "NULL IN (...)" siempre devuelve UNKNOWN, y "NOT IN" puede llevar a un resultado vacío si al menos uno de los elementos de la lista es NULL. El rendimiento en grandes datos con una condición mal escrita puede caer drásticamente.

Solución

La combinación correcta se ve así:

Si seleccionamos valores de la lista, complementamos con una condición para NULL si necesitamos devolverlos también:

SELECT * FROM sales
WHERE region IN ('Moscú','Samara') OR region IS NULL;

Para la negación usamos lógica explícita:

SELECT * FROM sales
WHERE (region NOT IN ('Moscú','Samara') OR region IS NULL);

Para excluir estrictamente los omitidos:

SELECT * FROM sales
WHERE region NOT IN ('Moscú','Samara') AND region IS NOT NULL;

Para grandes tablas, a menudo son relevantes los índices, especialmente si el filtrado por lista es una operación frecuente.

Características clave:

Gestionar el filtrado por NULL debe hacerse explícitamente, de lo contrario, se pierden filas.
El uso correcto de IN y NOT IN es crítico para selecciones correctas.
La indexación en los campos que participan en IN/NOT IN acelera significativamente el filtrado.

Preguntas engañosas.

¿Qué sucederá si se verifica "WHERE field NOT IN ('a', NULL)"?

El resultado siempre será vacío, ya que cualquier fila se compara con NULL en la lista, lo que da UNKNOWN y filtra todo.

Ejemplo de código:

SELECT * FROM test WHERE name NOT IN ('Iván', NULL);

¿Dará "IN (NULL,...)" valor para filas con NULL?

No. La expresión NULL IN (...) siempre devuelve UNKNOWN, no TRUE.

¿Cómo acelerar el filtrado por una gran lista de valores?

Usar índices y preferiblemente aplicar JOIN con una tabla temporal, si la lista es grande (cientos/miles de valores) — eso acelera el filtrado en comparación con un largo IN.

Errores comunes y anti-patrones

Escribir solo IN/NOT IN sin una condición EXPLÍCITA para NULL.
No tener en cuenta las peculiaridades de la ejecución de NOT IN con NULL.
No optimizar la indexación para el campo de filtrado.

Ejemplo de la vida real

Caso negativo

En un informe analítico se aplicó "WHERE city NOT IN ('Londres', field_2, NULL)". Resultado: completamente vacío, nadie pudo entender durante mucho tiempo la razón de las filas perdidas.

Ventajas:

Consulta corta y "legible".

Desventajas:

Pérdida de datos, lógica no obvia para los desarrolladores.

Caso positivo

En CRM se seleccionó una lista de regiones de clientes y se añadió un botón adicional "Incluir valores no completados". Lógica de filtrado:

... WHERE city IN ('SPB','NNov') OR city IS NULL