Uniones de tablas (2) — OUTER JOIN, CROSS JOIN, auto-unión

OUTER JOIN — conservar las filas que existen en un solo lado

El INNER JOIN de la vez pasada devolvía solo las filas que coincidían en ambas tablas, así que los empleados sin departamento y los departamentos sin empleados desaparecían del resultado. Cuando el requisito es «quiero todos los empleados; quienes no tienen departamento pueden llevar el nombre de departamento en blanco», usas un OUTER JOIN que siempre conserva las filas de un lado.

Hay 3 tipos de OUTER JOIN según el lado que conserves: LEFT JOIN conserva todas las filas de la tabla izquierda, RIGHT JOIN conserva todas las filas de la tabla derecha, y FULL OUTER JOIN conserva todas las filas de ambas. Las columnas del lado sin coincidencia se rellenan con NULL. La consola de este curso usa SQLite, y puedes ejecutar LEFT / RIGHT / FULL tal cual. Los datos son los mismos que la vez pasada: department (6 departamentos) y employee (30 empleados).

LEFT JOIN — conservar siempre la tabla izquierda

Cuando escribes SELECT cols FROM left LEFT JOIN right ON condition, se conserva cada fila de la tabla izquierda, y si no hay fila coincidente en la tabla derecha las columnas del lado derecho pasan a NULL. LEFT JOIN es la abreviatura de LEFT OUTER JOIN, y ambos se comportan igual. Lo usas cuando quieres listar el lado principal sin dejar a nadie fuera, como «mostrar todos los empleados, y dejar el nombre del departamento en blanco para quienes no tienen departamento».

-- Conservar todos los empleados. Si no hay departamento, dept_name es NULL
SELECT e.name, e.city, d.dept_name
FROM employee e
LEFT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

Imagina el requisito: «quiero encontrar empleados que no pertenecen a ningún departamento, alinear el dept_id del lado empleado y del lado departamento, y confirmar por qué no se puede buscar el nombre del departamento». (Ejecútala correctamente y aparecerá la explicación.)

① Haz LEFT JOIN de employee (alias e) a la izquierda y department (alias d) a la derecha, y reduce solo a las filas donde d.dept_id es NULL.

② Además de e.name y e.city, extrae `e.dept_id` (lado empleado) y `d.dept_id` / `d.dept_name` (lado departamento). Confirma visualmente que, como e.dept_id es NULL en sí mismo (sin departamento asignado), no hay pareja de unión, así que d.dept_id y d.dept_name también son NULL. Ordena por e.name ascendente.

③ Luego cambia la misma unión a un INNER JOIN y confirma que no vuelve ni una sola fila (porque las filas cuya clave de unión es NULL desaparecen con un INNER JOIN).

RIGHT JOIN y FULL OUTER JOIN

RIGHT JOIN es un LEFT JOIN con la izquierda y la derecha intercambiadas — conserva todas las filas de la tabla derecha. Cuando escribes employee e RIGHT JOIN department d ON ..., el departamento Legal sin ningún empleado se queda en el resultado, y las columnas del lado employee pasan a NULL. Es adecuado para «mostrar todos los departamentos, y mostrar los departamentos sin empleados con los campos en blanco».

FULL OUTER JOIN conserva todas las filas de ambas tablas. Las filas coincidentes se unen una al lado de la otra, las filas que existen solo a la izquierda (las 4 personas sin departamento) reciben NULL en el lado derecho, y las filas que existen solo a la derecha (el departamento Legal) reciben NULL en el lado izquierdo — todo alineado en un solo resultado.

-- RIGHT JOIN: conservar todos los departamentos. Legal tiene NULL en el lado empleado
SELECT d.dept_name, e.name
FROM employee e
RIGHT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

-- FULL OUTER JOIN: conservar tanto los empleados sin departamento como los departamentos sin empleados
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

Imagina el requisito: «quiero confirmar los departamentos donde no hay ni un solo empleado asignado, alineando las columnas del lado departamento y las columnas del lado empleado».

① Haz RIGHT JOIN de employee (alias e) a la izquierda y department (alias d) a la derecha, y reduce solo a las filas donde e.emp_id es NULL.

② Extrae d.dept_id / d.dept_name / d.location (lado departamento) y `e.emp_id` / `e.name` (lado empleado). Confirma que d.dept_id tiene un valor (el número del departamento) mientras que e.emp_id y e.name son NULL (en blanco) — lo que significa que el departamento existe de verdad pero tiene 0 empleados asignados. Ordena por d.dept_name ascendente.

Imagina el requisito: «quiero cotejar empleados y departamentos en una sola tabla, alinear el dept_id de ambos lados, y confirmar cómo las filas que existen en un solo lado pasan a NULL».

① Haz FULL OUTER JOIN de employee (alias e) y department (alias d). La condición de unión es una coincidencia de dept_id.

② Extrae e.emp_id / e.name / `e.dept_id` (lado empleado) y `d.dept_id` / `d.dept_name` (lado departamento). Las filas coincidentes tienen valores en ambos lados, pero los empleados sin departamento asignado tienen tanto `e.dept_id` como `d.dept_id` NULL, y el departamento sin empleados (Legal) tiene un valor en `d.dept_id` y NULL en el lado `e`. Confirma visualmente a qué patrón pertenece cada «fila de un solo lado».

③ Ordena por d.dept_id ascendente (las filas donde d.dept_id es NULL se agrupan hacia arriba).

CROSS JOIN — construir todas las combinaciones

CROSS JOIN no tiene condición de unión y construye todas las combinaciones de cada fila de la tabla izquierda con cada fila de la tabla derecha. El número de filas del resultado es número de filas izquierda × número de filas derecha (el producto cartesiano). Para employee (30 filas) y department (6 filas), eso son 30 × 6 = 180 filas.

En la práctica lo usas para enumerar todas las combinaciones posibles como «todos los empleados × todos los meses» o «todas las tiendas × todos los productos», luego le unes los valores reales y rellenas con 0 las celdas sin valor real — como base de una agregación. Como es una unión sin condición, no añades ON.

-- Contar el número de combinaciones todos-empleados x todos-departamentos
SELECT count(*) AS combo_count
FROM employee e
CROSS JOIN department d;

-- Inspeccionar parte de las combinaciones (solo las parejas con el departamento Sales)
SELECT e.name, d.dept_name
FROM employee e
CROSS JOIN department d
WHERE d.dept_name = 'Sales'
ORDER BY e.emp_id;

Imagina el requisito: «quiero contar cuántas combinaciones posibles hay entre todos los empleados y todos los departamentos».

① Haz CROSS JOIN de employee (alias e) y department (alias d) (no escribas una condición de unión).

② Cuenta el número total de combinaciones con count(*), y dale a la columna de resultado el alias combo_count.

Auto-unión — usar la misma tabla dos veces para buscar el nombre del jefe

La tabla employee tiene una columna manager_id (el número de empleado del jefe), y el jefe también es un empleado en la misma tabla employee. Cuando quieres «mostrar el nombre de cada empleado junto al nombre de su jefe», haces un JOIN de la misma tabla dos veces. Esto se llama una auto-unión.

Lo escribes como FROM employee e JOIN employee m ON e.manager_id = m.emp_id. Haces que la misma tabla aparezca dos veces con alias que separan los roles — e como «el lado empleado» y m como «el lado jefe». Los alias son obligatorios en una auto-unión; sin ellos no puedes saber qué employee es cuál. Con un INNER JOIN, los empleados sin jefe desaparecen, así que usa un LEFT JOIN si quieres listar todos los empleados.

-- Mostrar el nombre del jefe de cada empleado al lado (solo empleados que tienen jefe: INNER)
SELECT e.name AS employee, m.name AS manager
FROM employee e
JOIN employee m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Imagina el requisito: «quiero listar todos los empleados con el nombre de su jefe al lado. Los empleados sin jefe pueden llevar el nombre del jefe en blanco».

① Haz LEFT JOIN de employee como alias e (lado empleado) con employee otra vez como alias m (lado jefe). La condición de unión es e.manager_id = m.emp_id.

② Extrae 2 columnas, dando a e.name el alias employee y a m.name el alias manager.

③ Ordena por e.emp_id ascendente.

El NULL de un OUTER JOIN cambia de significado según dónde pongas el WHERE

Si después de un LEFT JOIN escribes una condición sobre una columna del lado derecho como WHERE right_table.column = value, NULL siempre se evalúa como falso en una comparación, así que las filas sin pareja se caen y obtienes en la práctica el mismo resultado que un INNER JOIN. Cuando quieres reducir conservando «las filas sin pareja», escribe esa condición en el lado ON, o usa WHERE right_table.key IS NULL para recoger explícitamente «las filas sin pareja». Para las comprobaciones de NULL, usa siempre IS NULL / IS NOT NULL, nunca = NULL.