Reescribir IN, EXISTS y subconsultas correlacionadas

Los datos que usamos en este artículo — perf_sales y employee

Dos consultas que devuelven el mismo resultado pueden producir planes de consulta distintos (los pasos que la base de datos eligió para qué tablas leer, en qué orden, y qué índices usar).

En este artículo aprenderás cuándo usar IN frente a EXISTS, cómo eliminar una subconsulta correlacionada (una subconsulta reevaluada para cada fila externa), y cómo reescribir "quédate solo con las filas que (sí o no) tienen coincidencia" — comparando planes de consulta con EXPLAIN QUERY PLAN sobre la marcha.

Antes de meternos en los ejercicios, vamos a revisar las definiciones de columna y los datos de muestra de las dos tablas usadas en este artículo — perf_sales y employee.

① Usa PRAGMA table_info(perf_sales); y PRAGMA table_info(employee); para confirmar las definiciones de columna de ambas tablas.

② Usa SELECT * FROM perf_sales LIMIT 5; y SELECT * FROM employee LIMIT 5; para previsualizar las primeras 5 filas de cada una. perf_sales tiene 50 000 filas, así que añade siempre LIMIT al previsualizar.

IN y EXISTS — dos formas de escribir la misma comprobación

Hay dos formas de preguntar "¿hay una fila coincidente en otra tabla?".

WHERE col IN (SELECT ...) comprueba si el valor está en el conjunto que devuelve la consulta interna, mientras que WHERE EXISTS (SELECT 1 FROM ... WHERE ...) comprueba si la consulta interna devuelve al menos una fila.

En muchos casos ambas producen el mismo resultado, y la base de datos puede tratar cualquiera como "quédate solo con las filas que tienen coincidencia".

Anteponer EXPLAIN QUERY PLAN query; muestra los pasos que la base de datos eligió, línea por línea.

SCAN perf_sales significa un recorrido completo de filas, SEARCH ... USING INDEX significa ir directamente a las filas necesarias vía un índice, y CORRELATED SCALAR SUBQUERY es una subconsulta que se reevalúa para cada fila externa.

-- Ejemplo: cuenta las ventas atendidas por empleados con dept_id=2, de dos formas

-- Forma IN
SELECT COUNT(*) FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 2);

-- Forma EXISTS (correlacionada)
SELECT COUNT(*) FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 2
);

-- Antepón EXPLAIN QUERY PLAN para ver el plan
-- EXPLAIN QUERY PLAN SELECT COUNT(*) FROM perf_sales WHERE ...;

Vamos a ser más concretos. ¿Cómo se comportan IN y EXISTS según el tamaño de la tabla externa y el tamaño de la subconsulta interna y si existe un índice?

Los dos casos de abajo — y qué forma es más rápida en cada uno — se resumen en el diagrama que sigue.

-- Caso A: externa perf_sales (50 000 filas), interna empleados con dept_id=1 (~5 filas, pequeño y fijo)

-- Forma IN
SELECT * FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 1);

-- Forma EXISTS
SELECT * FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 1
);

-- Caso B: externa employee (30 filas), interna perf_sales (50 000 filas, con un índice sobre emp_id)

-- Forma IN
SELECT * FROM employee e
WHERE e.emp_id IN (SELECT emp_id FROM perf_sales);

-- Forma EXISTS
SELECT * FROM employee e
WHERE EXISTS (
  SELECT 1 FROM perf_sales p
  WHERE p.emp_id = e.emp_id
);

Tip — las bases de datos modernas casi no muestran diferencia real de velocidad

Hemos clasificado las cosas en "IN tiende a ganar / EXISTS tiende a ganar", pero esa es una tendencia que viene de optimizadores más antiguos.

En bases de datos modernas como SQLite, PostgreSQL y MySQL 8.0+, tanto IN (SELECT ...) como un EXISTS correlacionado se reescriben al mismo plan de "quédate solo con las filas que tienen coincidencia", y el plan real y la velocidad en el mundo real acaban siendo aproximadamente iguales.

En otras palabras, puedes elegir la que se lea mejor.

Cuando la diferencia de plan o de velocidad sí importa, la regla es medir — EXPLAIN QUERY PLAN y tiempo de reloj.

Escribe "el conteo de perf_sales atendidas por empleados con dept_id 2" en dos formas — IN y EXISTS — y compara cada plan de consulta. (Ejecútalo correctamente para revelar la explicación.)

① Antepón EXPLAIN QUERY PLAN para mostrar el plan de la consulta de conteo en forma emp_id IN (SELECT ...).

② Luego muestra el plan para el mismo conteo escrito en forma EXISTS (correlacionando contra employee) — de nuevo con EXPLAIN QUERY PLAN.

③ Compara los dos planes, fijándote en las palabras SCAN / SEARCH / USING INDEX y demás.

Reescribir una subconsulta correlacionada como JOIN + agregación

Una subconsulta correlacionada en la lista SELECT (una que se reevalúa por cada fila externa) se llama cada vez más a medida que crece el número de filas.

En EXPLAIN QUERY PLAN aparece como una línea CORRELATED SCALAR SUBQUERY.

En muchos casos puede reescribirse como una sola agregación preparada de antemano y unida con JOIN — la reevaluación por fila desaparece.

El patrón de reescritura convierte "por cada fila externa, llama a (SELECT ... WHERE child.key = parent.key)" en "construye la tabla de agregación una vez con GROUP BY key, y luego haz JOIN con la padre".

El resultado es el mismo y la correlación desaparece del plan.

-- Antes: total de ventas por empleado vía una subconsulta correlacionada
SELECT e.emp_id, e.name,
  (SELECT SUM(p.amount) FROM perf_sales p
   WHERE p.emp_id = e.emp_id) AS total
FROM employee e
ORDER BY e.emp_id;

-- Después: construye la agregación una vez y haz JOIN
WITH sales_sum AS (
  SELECT emp_id, SUM(amount) AS total
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.name, s.total
FROM employee e
LEFT JOIN sales_sum s ON s.emp_id = e.emp_id
ORDER BY e.emp_id;

Ejecuta "una lista del total de perf_sales de cada empleado" de dos formas — como subconsulta correlacionada y como reescritura — y observa cómo cambia el plan.

① Antepón EXPLAIN QUERY PLAN para mostrar el plan de la versión que pone (SELECT SUM(amount) FROM perf_sales WHERE mismo emp_id) en la lista SELECT como subconsulta correlacionada.

② Pliega la misma agregación en una CTE que haga GROUP BY sobre emp_id una vez, hazle LEFT JOIN con employee y muestra también ese plan con EXPLAIN QUERY PLAN.

③ Confirma que la línea CORRELATED SCALAR SUBQUERY de la versión correlacionada desaparece en la versión reescrita.

La trampa NULL de NOT IN — evítala con NOT EXISTS

Cuando preguntas "ninguna fila coincidente" — una anti-unión — NOT IN (SELECT ...) tiene una trampa.

Si un solo NULL se cuela en el conjunto interno, la lógica de tres valores (TRUE / FALSE / UNKNOWN) hace que toda la condición sea UNKNOWN, y el resultado viene como 0 filas.

Mientras tanto, NOT EXISTS (SELECT 1 ... WHERE ...) solo pregunta si la interna produce una fila, así que NULL no la rompe y la anti-unión funciona correctamente.

Cuando escribas una anti-unión, usa NOT EXISTS — o, si tienes que usar NOT IN, añade WHERE col IS NOT NULL por dentro para sacar los NULL.

Este curso usa NOT EXISTS como forma estándar.

-- Construye un ejemplo con NULL en el conjunto interno, usando team
-- team(mgr_emp) incluye deliberadamente un NULL
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
-- Trampa: con NULL presente, NOT IN devuelve 0 filas
SELECT COUNT(*) FROM perf_sales
WHERE emp_id NOT IN (SELECT mgr_emp FROM team);

-- Arreglo: NOT EXISTS no se ve afectado por NULL, así que la anti-unión funciona
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
SELECT COUNT(*) FROM perf_sales p
WHERE NOT EXISTS (
  SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id
);

Observa — mediante conteos de filas — qué pasa cuando el conjunto interno contiene un NULL, comparando NOT IN y NOT EXISTS. Este es el último ejercicio del artículo. Construye una pequeña lista de valores llamada team (tres valores: 3, 7, NULL) como CTE, luego cuenta "las filas de perf_sales cuyo emp_id no está en esa lista" de dos formas.

① Construye una CTE con 3, 7, NULL usando WITH team(mgr_emp) AS (VALUES ...) y cuenta las filas con emp_id NOT IN (SELECT mgr_emp FROM team).

② Escribe la misma comprobación como NOT EXISTS (SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id) y cuenta las filas.

③ Compara los dos conteos y confirma que la versión NOT IN es 0.