EXISTS y subconsultas correlacionadas

Datos usados en este artículo — employee / sales / department

EXISTS es una subconsulta que comprueba solo si existe al menos una fila que coincide, y la usas de forma muy parecida a IN.

En este artículo recorrerás los fundamentos de EXISTS / NOT EXISTS, cómo evitar de forma segura la trampa de NOT IN + NULL escribiéndola con EXISTS, y cómo elegir entre EXISTS e IN según la legibilidad.

El material son tres tablas de datos de empleados.

Usando employee (30 empleados), sales (50 líneas de venta) y department (6 departamentos), extraerás "empleados con / sin ventas" y "departamentos sin empleados".

Antes de meterte en los ejercicios, echa un vistazo a las definiciones de columnas y los datos de muestra de las tres tablas que usa este artículo — employee / sales / department.

① Ejecuta PRAGMA table_info(...) para comprobar las definiciones de columnas de las tres tablas.

② Ejecuta SELECT * FROM table_name LIMIT 5; para previsualizar las primeras 5 filas de cada tabla. El hecho de que employee.dept_id pueda ser NULL también aparece en un ejercicio posterior.

EXISTS / NOT EXISTS — solo comprobar si existe una fila

WHERE EXISTS (SELECT ... ) es verdadero si la subconsulta devuelve aunque sea una fila, y falso si no devuelve ninguna fila.

Lo que selecciona la subconsulta no afecta al resultado, así que por convención escribes SELECT 1 (el valor no se usa — solo te importa si existe una fila).

La forma básica es una subconsulta correlacionada que referencia una columna de la consulta principal dentro del EXISTS.

"Empleados con ventas" es WHERE EXISTS (SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id), y "empleados sin ventas" es la negación de eso, NOT EXISTS.

EXISTS deja de evaluar en cuanto encuentra una fila, lo que lo hace adecuado para comprobaciones de existencia.

-- EXISTS: empleados con ventas
SELECT emp.emp_id, emp.name
FROM employee emp
WHERE EXISTS (
  SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id
)
ORDER BY emp.emp_id;

Imagina el requisito "quiero una lista de empleados que no aparecen ni una sola vez en las líneas de venta (empleados que aún no han registrado ninguna venta)". (Ejecútalo correctamente y aparecerá la explicación.)

① Da a la tabla employee el alias e y extrae emp_id y name.

② Usa NOT EXISTS para reducir a las filas donde ese empleado no tiene ninguna fila en sales. Correlaciona la subconsulta con la consulta principal en la forma SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id.

③ Ordena por emp_id en orden ascendente.

La trampa de NOT IN + NULL — EXISTS es seguro

Si escribes "filtrado negativo" con NOT IN, el resultado puede salir como 0 filas cuando se mezcla un NULL en el lado de la subconsulta.

x NOT IN (1, 2, NULL) se evalúa como "x no es 1, no es 2 y no es NULL", pero x <> NULL nunca se vuelve verdadero y siempre es NULL (desconocido), así que el NOT IN completo nunca se vuelve verdadero.

El razonamiento completo se explica en Subconsultas ① (la trampa de NOT IN + NULL).

Como employee.dept_id mezcla NULL, escribir "departamentos sin empleados" como WHERE dept_id NOT IN (SELECT dept_id FROM employee) hace que Legal (un departamento sin ningún empleado), que debería estar ahí, desaparezca y el resultado salga como 0 filas.

NOT EXISTS solo mira si existe una fila y no se ve afectado por NULL, así que en esta situación NOT EXISTS devuelve el resultado correcto.

-- Trampa: una subconsulta que contiene NULL + NOT IN sale como 0 filas
SELECT dept_id, dept_name
FROM department
WHERE dept_id NOT IN (SELECT dept_id FROM employee);
-- → 0 filas porque employee.dept_id contiene NULL

-- Seguro: NOT EXISTS no se ve afectado por NULL
SELECT d.dept_id, d.dept_name
FROM department d
WHERE NOT EXISTS (
  SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id
);

Imagina el requisito "quiero listar los departamentos que no tienen ningún empleado asignado". Como employee.dept_id mezcla NULL, escríbelo de forma segura con NOT EXISTS.

① Da a la tabla department el alias d y extrae dept_id y dept_name.

② Usa NOT EXISTS para reducir a las filas donde ese departamento no tiene ninguna fila de empleado en employee. Correlaciona la subconsulta en la forma SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id.

③ Ordena por dept_id en orden ascendente.

Comprueba qué pasa cuando escribes el ejercicio anterior con NOT IN. Este ejercicio está pensado para que experimentes el error en carne propia, así que ejecútalo como NOT IN y observa que el resultado sale como 0 filas.

① Extrae dept_id y dept_name de la tabla department.

② Intenta listar "departamentos sin empleados" usando WHERE dept_id NOT IN (SELECT dept_id FROM employee).

③ Ejecútalo y confirma que el resultado es 0 filas (el departamento Legal no aparece). Piensa por qué el resultado difiere de la versión con NOT EXISTS de la Práctica 2.

Elige EXISTS por legibilidad

EXISTS solo mira si existe una fila, así que es seguro incluso cuando hay NULL, pero escribir una subconsulta correlacionada puede hacer que parezca más largo que IN.

Para una comprobación de existencia simple hay situaciones en las que IN se lee mejor, y en el trabajo real eliges según el contexto.

Por otro lado, para una negación como NOT IN donde NULL rompe el resultado, o cuando quieres juzgar de forma fiable solo si existe una fila, EXISTS / NOT EXISTS es la opción segura.