Cláusula WITH — Organiza tu consulta con resultados intermedios con nombre

Datos usados en este artículo — employee y department

Una CTE (Common Table Expression) es un mecanismo en el que escribes WITH nombre AS (SELECT ...) para darle un nombre a una subconsulta y luego referencias ese nombre en las consultas que siguen.

Como puedes dividir una subconsulta muy anidada en etapas, tu código se vuelve mucho más fácil de leer.

El material es la tabla employee (30 filas; la columna manager_id apunta al emp_id del manager, una estructura autorreferencial) y la tabla department (6 filas).

Trabajarás ejercicios que usan CTEs para escribir agregaciones en varias etapas de forma más legible.

Antes de empezar los ejercicios, mira las definiciones de columnas y los datos de ejemplo de las dos tablas que usa este artículo — employee y department.

① Ejecuta PRAGMA table_info(employee); y PRAGMA table_info(department); para revisar las definiciones de columnas de ambas tablas.

② Ejecuta SELECT * FROM employee LIMIT 5; y SELECT * FROM department LIMIT 5; para previsualizar las primeras 5 filas de cada una. Fíjate también en cómo aparece NULL en la columna manager_id (empleados que no tienen manager).

Construye un resultado intermedio con nombre usando WITH — haz legibles las consultas de varias etapas

Cuando escribes WITH nombre AS (SELECT ...), el resultado del SELECT interno recibe un nombre y puedes referenciarlo desde FROM o JOIN en la consulta principal que sigue.

En lugar de escribir el proceso de dos pasos «primero construir un resultado intermedio y luego usarlo para producir el resultado final» como una subconsulta anidada, puedes partirlo en una forma que se lee de arriba abajo.

Una CTE es un nombre temporal que desaparece en cuanto termina la consulta principal — no crea una tabla real.

La sintaxis es WITH nombre AS ( ... ) SELECT ... FROM nombre ...;.

-- Agrupa a los high earners en la CTE high_earner,
-- luego haz join con department para adjuntar el nombre del departamento
WITH high_earner AS (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
)
SELECT h.name, h.salary, d.dept_name
FROM high_earner h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

En el código de arriba, el nombre high_earner aísla el resultado intermedio «empleados con salary >= 7.000.000» y la consulta principal lo referencia como una tabla con FROM high_earner.

Así se ve ese flujo como diagrama.

Puedes escribir el mismo procesamiento con una subconsulta (un SELECT anidado dentro de los paréntesis de otro SELECT).

Reconstruyamos la versión anterior con WITH como una subconsulta en lugar de una CTE.

-- El mismo procesamiento escrito como subconsulta (tabla derivada)
-- Un SELECT vive dentro de los paréntesis de FROM, así que lees exterior → interior → exterior
SELECT h.name, h.salary, d.dept_name
FROM (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
) AS h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

El resultado es exactamente el mismo que la versión con WITH, pero con la versión con subconsulta tienes que meterte dentro de un SELECT anidado en FROM para entender qué hace el SELECT interno.

La versión con WITH aísla el resultado intermedio con el nombre `high_earner`, así que las dos etapas «① filtrar a los high earners → ② adjuntar el nombre del departamento» se leen directamente de arriba abajo.

La diferencia crece a medida que añades más etapas intermedias — para cuando tienes 3 o 4 etapas, las subconsultas muy anidadas se vuelven difíciles de seguir.

Requisito: «Quiero aislar a los empleados que no tienen manager (empleados de nivel ejecutivo con manager_id = NULL) y listarlos junto con sus nombres de departamento.» Resuélvelo en dos etapas con una CTE. (Si lo ejecutas correctamente, aparecerá la explicación.)

① Con WITH, define una CTE con nombre executive que extraiga de employee las filas donde manager_id sea NULL (emp_id, name, dept_id).

② Haz LEFT JOIN entre esa CTE y department por dept_id y devuelve name, dept_id, dept_name.

③ Ordena el resultado por name ascendente.

Encadena varias cláusulas WITH — divide la agregación en varias etapas, una a una

Tras WITH puedes listar varias CTEs separadas por comas.

La forma es WITH a AS (...), b AS (...) SELECT ..., y una CTE posterior puede referenciar a una anterior.

Esto te permite dividir una agregación en varias etapas («calcular la media por departamento» → «elegir empleados por encima de esa media» → «adjuntar el nombre del departamento») en etapas con nombre, una a una, de forma que incluso en una consulta larga puedes mantener separado el rol de cada etapa.

-- ① dept_avg: salario medio por departamento
-- ② top_earner: empleados por encima de la media de su propio departamento
-- ③ Consulta principal: adjunta el nombre del departamento y muestra
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
top_earner AS (
  SELECT e.name, e.dept_id, e.salary
  FROM employee e
  JOIN dept_avg da ON e.dept_id = da.dept_id
  WHERE e.salary > da.avg_salary
)
SELECT d.dept_name, t.name, t.salary
FROM top_earner t
JOIN department d ON t.dept_id = d.dept_id
ORDER BY d.dept_name, t.salary DESC;

Requisito: «Quiero calcular el salario medio de cada departamento, y luego listar solo los empleados cuyo salario supere esa media, con el nombre del departamento adjunto.» Resuélvelo encadenando dos CTEs.

① En la primera CTE, agrega employee por dept_id y produce el salario medio por departamento. Excluye de la media a los empleados cuyo dept_id sea NULL.

② En la segunda CTE, haz join entre employee y la primera CTE por dept_id y elige a los empleados (name, dept_id, salary) cuyo salary sea mayor que la media de su departamento.

③ En la consulta principal, haz join entre la segunda CTE y department por dept_id y devuelve dept_name, name, salary. Ordena por nombre de departamento ascendente, y dentro del mismo departamento por salario descendente.