Componer consultas — combinar JOIN, subconsultas y UNION

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

JOIN (conectar varias tablas), las subconsultas (una consulta dentro de una consulta) y UNION (apilar resultados verticalmente) funcionan cada uno por su cuenta, pero cuando los combinas con WHERE / ORDER BY / LIMIT puedes escribir consultas prácticas del mundo real.

En este artículo recorrerás, en orden, cómo combinar estas técnicas y hacerlas crecer en una sola consulta.

El material son tres tablas de empleados — employee (30 empleados), department (6 departamentos) y sales (50 líneas de venta).

Empezarás con un JOIN simple, y luego construirás a través de subconsultas y UNION paso a paso.

Antes de meterte en los ejercicios, tómate un momento para comprobar las definiciones de columnas y una muestra de los datos de las tres tablas usadas en este artículo — employee / department / sales.

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

② Usa SELECT * FROM table_name LIMIT 5; para previsualizar las primeras 5 filas de cada tabla.

Fija el orden en que se evalúan las cláusulas — JOIN, WHERE, ORDER BY, LIMIT

El orden en que escribes una consulta (SELECT … FROM … WHERE … ORDER BY) es diferente del orden en que se evalúa.

La base de datos primero conecta tablas con FROM / JOIN, luego reduce filas con WHERE, agrega con GROUP BY / HAVING, construye columnas con SELECT, y finalmente ordena con ORDER BY y limita el número de filas con LIMIT.

Una vez que dominas este orden, reglas como "para filtrar sobre un resultado agregado usa HAVING, no WHERE" y "LIMIT surte efecto después de ordenar" empiezan a tener sentido.

-- JOIN + WHERE + ORDER BY: empleados de departamentos en Osaka, ordenados por fecha de contratación
SELECT emp.name, dept.dept_name, emp.hired_on
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE dept.location = 'Osaka'
ORDER BY emp.hired_on;

Combina JOIN, WHERE, ORDER BY y LIMIT en una sola consulta. (Ejecútalo correctamente y aparecerá la explicación.)

① Une internamente employee y department por dept_id y extrae el nombre del empleado, el nombre del departamento y el salario.

② Reduce solo a los empleados cuya ubicación de departamento (department.location) es Tokyo.

③ Ordena por salario de mayor a menor y toma solo los 5 primeros.

Construir un valor de referencia con una subconsulta y reducir el resultado del JOIN

Una subconsulta se puede usar para construir el "valor de referencia" que necesitas para reducir aún más un resultado que ensamblaste con JOIN.

Un valor de criterio que cambia por empleado, como "el salario promedio de mi propio departamento", se calcula con una subconsulta correlacionada (una subconsulta que se recalcula para cada empleado individual) que referencia la fila exterior.

Cuando adjuntas el nombre del departamento con JOIN y reduces filas usando una condición de subconsulta correlacionada, varias piezas de sintaxis encajan juntas en una sola consulta.

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

Esta vez apilarás subconsultas. Lista, con el nombre del departamento, a los empleados de cada departamento que ganan "más que el salario promedio de su propio departamento".

① Une employee y department y extrae el nombre del empleado, el nombre del departamento y el salario.

② Usa una subconsulta correlacionada para calcular el salario promedio del propio departamento de ese empleado, y reduce solo a los empleados cuyo salary es mayor que él.

③ Ordena por dept_id ascendente, y dentro del mismo departamento por salario de mayor a menor.

Combinar dos perspectivas con UNION y rematar con ORDER BY

UNION apila los resultados de dos SELECT verticalmente.

El SELECT superior y el inferior deben ser compatibles UNION (el mismo número de columnas, con los tipos de las columnas correspondientes siendo compatibles).

UNION colapsa en una las filas totalmente idénticas (eliminando duplicados), mientras que UNION ALL conserva los duplicados tal cual.

La ordenación y los límites de número de filas se aplican al resultado combinado en su conjunto, así que escribe ORDER BY / LIMIT una sola vez, al final del todo.

-- UNION ALL: apila dos perspectivas verticalmente con una columna de etiqueta (conserva duplicados)
SELECT name, 'Kyoto' AS via FROM employee WHERE city = 'Kyoto'
UNION ALL
SELECT emp.name, 'HighSales' AS via
FROM employee emp
JOIN sales sale ON sale.emp_id = emp.emp_id
GROUP BY emp.emp_id
HAVING SUM(sale.amount) >= 1500000
ORDER BY name;

Por último, combina dos perspectivas en una sola lista con UNION.

① Escribe un SELECT que extraiga los nombres de los "empleados que viven en Kyoto".

② Escribe un SELECT que encuentre a los "empleados cuyas ventas totales son 1.5 millones de yenes o más" usando una unión de employee y sales más agregación (GROUP BY / HAVING).

③ Conecta los dos con UNION para colapsar los duplicados en una sola fila, y ordena por nombre ascendente.