Uniones de tablas (3) — Apilar WHERE / ORDER BY / CASE sobre un JOIN

Los datos que usaremos — department, employee y sales

En los artículos anteriores viste las distintas variantes de unión — INNER JOIN, OUTER JOIN y auto-unión.

En este artículo vas a apilar filtros WHERE, ORDER BY y etiquetas de franja con CASE sobre un JOIN y construir la forma de un informe de agregación que vas a encontrar de verdad en el trabajo.

Los datos son department (6 departamentos) y employee (30 personas), más una nueva tabla sales (50 filas) con las líneas de venta.

sales.emp_id es una clave foránea que apunta a employee.emp_id.

Uniremos las tres tablas para construir un informe que imprime el departamento y el nombre del representante junto a cada venta.

Antes de los ejercicios, confirmemos las definiciones de columnas y las filas de muestra de las tres tablas — department, employee y sales.

① Ejecuta PRAGMA table_info(nombre_tabla); en cada tabla para inspeccionar sus columnas.

② Ejecuta SELECT * FROM nombre_tabla LIMIT 5; en cada tabla para ver las primeras 5 filas. Fíjate cómo sales.emp_id apunta a una fila de employee.

Unir tres tablas — encadenar JOINs

Para unir tres o más tablas solo tienes que encadenar cláusulas `JOIN ... ON ...` una tras otra.

Escribe FROM sales s JOIN employee e ON s.emp_id = e.emp_id JOIN department d ON e.dept_id = d.dept_id — primero enlaza sales con employee por emp_id, luego enlaza ese resultado con department por dept_id.

La forma más natural de leer el orden es: «parte de las líneas de venta (sales) y luego añade la información del empleado y del departamento sobre ellas».

Todo es INNER JOIN, así que solo sobreviven las filas que coinciden en las tres tablas.

En este conjunto de datos los 12 empleados con ventas pertenecen a algún departamento, así que el INNER JOIN de 3 tablas devuelve las 50 filas de sales.

-- Añade el nombre del representante y dept_name a cada venta (INNER JOIN de 3 tablas)
SELECT s.sale_id, e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY s.sale_id;

Imagina el requisito: «Quiero un informe de las líneas de venta con el nombre del empleado responsable y el nombre de su departamento adjuntos». (La explicación aparece una vez que tu consulta se ejecuta correctamente.)

① Haz INNER JOIN de sales (con alias s) con employee (alias e) por emp_id, y luego INNER JOIN de ese resultado con department (alias d) por dept_id.

② Selecciona las cuatro columnas s.sale_id, e.name, d.dept_name y s.amount.

③ Ordena el resultado por s.sale_id ascendente.

Filtrar con WHERE, ordenar con ORDER BY

Una vez que el JOIN está en su sitio, usa WHERE para conservar solo las filas que necesitas y ORDER BY para disponerlas de forma legible.

Las condiciones de WHERE pueden referirse a columnas de cualquier tabla unida.

WHERE d.location = 'Tokyo' conserva solo las ventas de departamentos en Tokyo, y WHERE s.amount >= 400000 conserva solo las ventas de alto valor.

Las cláusulas SQL se escriben en el orden FROM → JOIN → ON → WHERE → ORDER BY.

WHERE filtra las filas después de que el JOIN se completa, y ORDER BY ordena al final.

Para ordenar por varias columnas, sepáralas con coma: ORDER BY col1 DESC, col2. Las filas con empate en col1 se ordenan luego por col2 (una clave secundaria para estabilizar el orden).

-- Solo las ventas de departamentos con sede en Osaka, mayor importe primero
SELECT e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.location = 'Osaka'
ORDER BY s.amount DESC, s.sale_id;

Imagina el requisito: «Quiero un informe de las ventas en departamentos con sede en Tokyo por valor de 400.000 o más, ordenadas por importe descendente».

① Haz INNER JOIN de sales (s), employee (e) y department (d) por emp_id y dept_id.

② Filtra a las filas donde d.location sea Tokyo y s.amount sea al menos 400000.

③ Selecciona las tres columnas e.name, d.dept_name y s.amount, ordenadas por s.amount descendente. Para filas empatadas por importe, usa s.sale_id ascendente como clave secundaria para estabilizar el orden.

Añade etiquetas de franja con CASE — termina con un informe clasificado

Sobre JOIN, WHERE y ORDER BY, una expresión CASE puede añadir una columna con etiquetas de franja para el importe, convirtiendo el resultado en un informe que una persona puede leer de un vistazo.

CASE evalúa condiciones de arriba abajo y devuelve el valor de la primera rama que resulte verdadera. Puedes ponerlo directamente dentro de la lista de columnas del SELECT (forma con búsqueda: CASE WHEN condición THEN valor ... ELSE valor_por_defecto END).

Por ejemplo, divide cada venta en tres franjas por amount: High cuando amount >= 400000, Mid cuando amount >= 200000 y Low en otro caso — exponiendo la franja en una columna con alias band.

Mantén las etiquetas como cadenas ASCII fijas ('High' / 'Mid' / 'Low') para que el resultado siga siendo portable entre entornos.

Con JOIN de 3 tablas, WHERE, ORDER BY y CASE combinados en una sola consulta, acabas teniendo casi exactamente la forma de los informes de agregación que se construyen cada día en equipos reales.

-- Ejemplo: divide amount en 2 franjas (Large >= 300000)
SELECT e.name, d.dept_name, s.amount,
  CASE
    WHEN s.amount >= 300000 THEN 'Large'
    ELSE 'Small'
  END AS size
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
ORDER BY s.sale_id;

Imagina el requisito: «Quiero un informe de ventas con el nombre del representante, el nombre del departamento y una etiqueta de franja, ordenado por importe descendente».

① Haz INNER JOIN de sales (s), employee (e) y department (d) a través de las tres tablas.

② Añade una columna con alias band usando un CASE con búsqueda que devuelva 'High' cuando s.amount sea al menos 400000, 'Mid' cuando sea al menos 200000 y 'Low' en otro caso.

③ Selecciona las cuatro columnas e.name, d.dept_name, s.amount y band, ordenadas por s.amount descendente con s.sale_id ascendente como criterio de desempate.

Imagina el requisito: «Quiero un resumen que totalice las ventas de cada representante y los clasifique como Gold / Silver / Bronze según el total».

① Haz INNER JOIN de sales (s), employee (e) y department (d).

② Agrupa por e.name y d.dept_name, y agrega SUM(s.amount) con alias total.

③ Añade una columna con alias tier usando un CASE con búsqueda que devuelva 'Gold' cuando SUM(s.amount) sea al menos 1500000, 'Silver' cuando sea al menos 1000000 y 'Bronze' en otro caso.

④ Selecciona las cuatro columnas e.name, d.dept_name, total y tier, ordenadas por total descendente con e.name ascendente como criterio de desempate.

Los empleados sin ventas desaparecen con INNER JOIN

Todos los informes de este artículo usan INNER JOIN, así que los 18 empleados sin ninguna venta nunca aparecen en el resultado.

Si necesitas un informe que incluya a todos los empleados — con ceros para los que no tuvieron ventas — empieza con employee como columna vertebral y haz LEFT JOIN de sales sobre ella, luego envuelve el SUM en COALESCE(SUM(s.amount), 0) para convertir los totales NULL en 0.

Siempre que los números de un informe no cuadren, lo primero que hay que sospechar es que «el tipo de unión equivocado está descartando en silencio filas que deberían estar ahí».