Subconsultas ① — Escalar, IN y agregados en WHERE

Datos usados en este artículo — la tabla employee

Una subconsulta es una sentencia SELECT escrita dentro de otra consulta.

Puedes calcular primero "el salario promedio de todos los empleados" y luego usar ese resultado para reducir a "los empleados que ganan más que el promedio" — una consulta paso a paso escrita en una sola sentencia.

En este artículo recorreremos una por una las tres formas que se usan en la cláusula WHERE — subconsultas escalares, subconsultas con IN y agregados dentro de WHERE (subconsultas correlacionadas).

Antes de los ejercicios, veamos las definiciones de columnas y una muestra de los datos de la tabla employee.

① Ejecuta PRAGMA table_info(employee); para comprobar los nombres de columnas, los tipos y la clave primaria.

② Ejecuta SELECT * FROM employee LIMIT 5; para previsualizar las primeras 5 filas. Fíjate en que algunos empleados tienen NULL en la columna dept_id — lo trataremos en un ejercicio posterior.

Subconsulta escalar — usar un resultado de una fila y una columna como valor

Una subconsulta escalar (una subconsulta cuyo resultado es exactamente una fila y una columna) devuelve un único valor, así que puedes escribirla directamente en el lado derecho de un operador de comparación como = o >.

(SELECT AVG(salary) FROM employee) devuelve un valor — "el salario promedio de todos los empleados" — así que WHERE salary > (SELECT AVG(salary) FROM employee) reduce a "los empleados que ganan más que el promedio."

Los puntos clave son envolverla entre paréntesis y asegurarte de que siempre quepa en una fila y una columna (devolver varias filas es un error).

Las funciones de agregación como AVG / MAX / MIN / COUNT devuelven un único valor, así que se usan habitualmente en subconsultas escalares.

-- Subconsulta escalar: obtener los empleados que ganan el salario máximo
SELECT name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

Imagina el requisito "lista los empleados cuyo salario es mayor que el salario promedio de todos los empleados". (Ejecútalo correctamente y aparecerá la explicación.)

① Toma las 2 columnas name y salary de la tabla employee.

② En la condición WHERE, reduce a las filas donde salary es mayor que el salario promedio de todos los empleados. Calcula el salario promedio con una subconsulta escalar.

③ Ordena por salary en orden descendente.

Subconsulta con IN — filtrar por valores que viven en otra tabla

Cuando una subconsulta devuelve varias filas en una sola columna, usa IN en lugar de =.

Escribir WHERE column IN (SELECT another_column FROM another_table ...) conserva solo las filas cuyo valor está en el conjunto que devuelve la subconsulta.

La subconsulta a la derecha de IN hace SELECT de una sola columna (devolver varias columnas es un error).

Para negarlo usas NOT IN, pero hay una trampa: si se mezclan NULL en el lado de la subconsulta, el resultado cambia (lo cubrimos al final del artículo).

-- Subconsulta con IN: empleados de un departamento ubicado en Tokyo
SELECT name, dept_id
FROM employee
WHERE dept_id IN (
  SELECT dept_id FROM department WHERE location = 'Tokyo'
);

Imagina el requisito "lista los empleados que aparecen al menos una vez en los registros de ventas (empleados que realmente registraron ventas)".

① Toma las 2 columnas emp_id y name de la tabla employee.

② Construye con una subconsulta el conjunto de IDs de empleado que aparecen en la tabla sales, y usa IN para reducir a las filas donde emp_id está en ese conjunto.

③ Ordena por emp_id en orden ascendente.

Agregados dentro de WHERE — comparar contra promedios de departamento con una subconsulta correlacionada

Antes de las subconsultas correlacionadas, dejemos clara la estructura de dos niveles de una subconsulta.

Hay un SELECT principal que extrae la lista de empleados, y dentro de él, entre paréntesis, escribes otro SELECT (la subconsulta).

Las subconsultas hasta ahora eran subconsultas no correlacionadas — calculadas una sola vez al principio, de forma independiente del SELECT principal (por ejemplo, el salario promedio de toda la empresa es el mismo valor único sin importar qué empleado mires).

En cambio, una subconsulta que usa una columna del empleado que el SELECT principal está procesando actualmente se llama subconsulta correlacionada (una subconsulta que se recalcula para cada empleado).

Por ejemplo, para listar "el que más gana del departamento (el empleado cuyo salario es igual al máximo de su propio departamento)", el salario máximo difiere por departamento, así que para cada empleado necesitas el salario máximo de solo ese departamento.

En el ejemplo de abajo, el empleado principal lleva el alias emp y el lado de la subconsulta dept_member, y WHERE dept_member.dept_id = emp.dept_id los correlaciona.

Cuando cambia el empleado, el dept_id que se pasa también cambia, y el resultado se recalcula.

-- Subconsulta correlacionada: empleados cuyo salario es igual al máximo de su propio departamento (el que más gana del departamento)
SELECT emp.name, emp.dept_id, emp.salary
FROM employee emp
WHERE emp.salary = (
    SELECT MAX(dept_member.salary)
    FROM employee dept_member
    WHERE dept_member.dept_id = emp.dept_id
  );

Imagina el requisito "dentro de cada departamento, lista los empleados cuyo salario es mayor que el salario promedio de ese departamento".

① Pon el alias emp a la tabla employee y toma las 3 columnas name, dept_id y salary.

② Limita a las filas donde dept_id no es NULL (los empleados sin departamento quedan fuera del alcance).

③ Reduce a las filas donde salary es mayor que el salario promedio del mismo departamento que ese empleado. Calcula el promedio del departamento con una subconsulta con alias dept_member, correlacionada con dept_member.dept_id = emp.dept_id.

④ Ordena por dept_id ascendente, y dentro del mismo departamento por salary descendente.

La trampa de NOT IN y NULL

La negación de IN es NOT IN, pero si se incluye NULL en la subconsulta a la derecha de NOT IN, el resultado cambia drásticamente.

Eso es porque cuando se convierte en NOT IN (..., NULL), SQL comprueba "el valor no es igual a ninguno de ellos" en la forma value <> ... AND value <> NULL, pero value <> NULL no se resuelve ni como verdadero ni como falso (es NULL), así que el AND completo nunca se vuelve verdadero y ninguna fila sobrevive.

Imagina el requisito "lista los nombres de los empleados que no son jefe (manager) de nadie". La columna manager_id de employee contiene NULL para los empleados que no tienen jefe. (Ejecútalo correctamente y aparecerá la explicación.)

① Ejecuta el NOT IN del editor tal cual y confirma que el resultado sale como 0 filas.

② Excluye las filas donde manager_id es NULL en el lado de la subconsulta, y arréglalo para que los empleados que no son jefe de nadie salgan correctamente.

③ Ordena por emp_id en orden ascendente.