Combinación de tablas (1) — INNER JOIN y alias

Los datos usados en este artículo — department y employee

Hasta ahora has trabajado con una sola tabla a la vez, pero los datos del mundo real están repartidos en varias tablas. Los nombres y salarios de los empleados viven en la tabla employee, los nombres y ubicaciones de los departamentos viven en la tabla department — el diseño habitual reparte los datos por rol y los enlaza con employee.dept_id apuntando a department (esta «columna que apunta a una fila de otra tabla» se llama clave foránea).

En este artículo aprenderás el INNER JOIN, la forma más básica de un JOIN que combina dos tablas separadas en un solo resultado. El material es la tabla department (6 departamentos) y la tabla employee (30 empleados). A través de un ejercicio que construye una lista mostrando el nombre del departamento de cada empleado, probarás por turnos los alias de tabla, ON y USING.

Antes de lanzarte a los ejercicios, echa un vistazo a las definiciones de columnas y a los datos de ejemplo de las dos tablas que usa este artículo — department y employee.

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

② Ejecuta SELECT * FROM department LIMIT 5; y SELECT * FROM employee LIMIT 5; para previsualizar las 5 primeras filas. Fíjate en que algunas filas de employee tienen NULL en la columna dept_id.

INNER JOIN — conectar dos tablas con una condición de combinación

La tabla employee solo contiene dept_id (el número de departamento). Cuando quieres que la lista de empleados muestre también el nombre del departamento, tomas las filas donde employee.dept_id y department.dept_id coinciden, las alineas lado a lado y las fusionas en una sola fila. Esto es un JOIN, y el más básico es el INNER JOIN (INNER = combinación interna).

La forma es SELECT columnas FROM tabla_izquierda JOIN tabla_derecha ON condición_combinación. JOIN es la abreviatura de INNER JOIN — ambos se comportan igual. En ON escribes la condición de combinación, que dice «si tal columna coincide con tal columna, trata estas filas como la misma fila». INNER JOIN conserva solo las filas que coincidieron en ambas tablas; las filas sin contraparte en un lado desaparecen del resultado.

-- Mostrar dept_name junto a cada empleado (INNER JOIN)
SELECT employee.name, employee.salary, department.dept_name
FROM employee
JOIN department
  ON employee.dept_id = department.dept_id;

Imagina un requisito: «construir una tabla para la lista de empleados con el número de empleado, el nombre y el nombre del departamento al que pertenecen». (Ejecútala correctamente y aparecerá la explicación.)

① INNER JOIN la tabla employee y la tabla department. La condición de combinación es que dept_id coincida en ambas tablas.

② Extrae tres columnas: emp_id y name de employee, y dept_name de department.

③ Ordena el resultado por emp_id ascendente.

Alias de tabla — escribir cortos los nombres de tabla largos

Escribir el nombre de la tabla cada vez, como en employee.name o department.dept_name, alarga las consultas. Si pones un alias después del nombre de la tabla, como en FROM employee e, puedes referirte a ella con un nombre corto como e.name a partir de entonces. Escribirlo con AS, como FROM employee AS e, significa lo mismo.

Los alias también se vuelven necesarios para una autocombinación (usar la misma tabla dos veces, tratada en el próximo artículo) y para nombrar un resultado en una subconsulta. En este artículo escribiremos employee como e y department como d.

-- Alias employee como e y department como d
SELECT e.name, e.city, d.dept_name, d.location
FROM employee e
JOIN department d
  ON e.dept_id = d.dept_id;

Imagina un requisito: «mostrar solo los nombres de los empleados del departamento Engineering, junto con la ubicación de ese departamento».

① INNER JOIN employee con el alias e y department con el alias d. La condición de combinación es que dept_id coincida.

② Restringe a las filas donde dept_name es Engineering.

③ Extrae dos columnas — name de e y location de d — ordenadas por name ascendente.

USING — un atajo cuando los nombres de columna de combinación coinciden

employee y department se combinan por el mismo nombre de columna, `dept_id`, en ambas tablas. Cuando el nombre de la columna usada para la combinación es exactamente el mismo en ambas tablas como aquí, puedes escribir USING (dept_id) en lugar de ON e.dept_id = d.dept_id.

USING (nombre_columna) significa lo mismo que ON izquierda.nombre_columna = derecha.nombre_columna, y es más corto porque escribes la columna de combinación una sola vez. Además, una columna combinada con USING aparece una sola vez en el resultado, y puedes referenciarla directamente con SELECT dept_id (sin nombre de tabla). Cuando los nombres de columna difieren (por ejemplo, e.dept_id y d.id), no puedes usar USING — usas ON.

-- Escribir el mismo sentido que ON e.dept_id = d.dept_id con USING
SELECT e.name, dept_id, d.dept_name
FROM employee e
JOIN department d
  USING (dept_id);

Imagina un requisito: «de entre todos los empleados, extraer el top 5 por salario y listar su nombre, el nombre del departamento y el salario».

① Combina employee (alias e) y department (alias d) con la cláusula `USING`. La columna de combinación es dept_id, común a ambas tablas.

② Extrae tres columnas: name de e, dept_name de d y salary de e.

③ Ordena por salary descendente y restringe a las 5 primeras filas.

INNER JOIN descarta las filas que existen solo en un lado

INNER JOIN devuelve solo las filas donde la condición de combinación coincidió en ambas tablas. Las 4 personas cuyo dept_id es NULL (Ivan, Quinn, Xander, Brian) y el departamento Legal sin ningún empleado no tienen pareja de combinación, así que no aparecen en el resultado. «Quiero todos los empleados pero solo vuelven 26 filas» es, en la mayoría de los casos, causado por este comportamiento. Cuando quieres conservar también las filas que existen solo en un lado, usa el OUTER JOIN tratado en el próximo artículo.