Aprende SQL · lección gratuita

Lección 19 · FULL OUTER JOIN y CROSS JOIN

Resumen

Quedan dos tipos de JOIN para completar el repertorio. El FULL OUTER JOIN conserva todas las filas de ambas tablas, rellenando con NULL el lado que no tenga pareja: es la unión de un LEFT y un RIGHT. El CROSS JOIN no empareja nada: produce el producto cartesiano (todas las combinaciones posibles), útil para generar matrices o rejillas. SQLite moderno soporta ambos.

• FULL OUTER JOIN: filas con pareja + filas de la izquierda sin pareja + filas de la derecha sin pareja. Sirve para reconciliar dos conjuntos y ver qué hay en uno, en otro o en ambos.
• CROSS JOIN: cada fila de A se combina con cada fila de B. n × m filas. No lleva ON.
• Detectar discrepancias: un FULL JOIN con WHERE a.id IS NULL OR b.id IS NULL muestra exactamente lo que no casa entre las dos tablas.
• Rejillas con CROSS JOIN: combinarlo con un LEFT JOIN permite generar todas las combinaciones (p.ej. mes × canal) y luego rellenar las que tengan datos, mostrando ceros donde no los hay.
• Cuidado con el tamaño: un CROSS JOIN sobre tablas grandes explota en filas; úsalo con tablas pequeñas o de catálogo.

Sintaxis / Conceptos

a FULL OUTER JOIN b ON ... equivale conceptualmente a (a LEFT JOIN b) UNION (a RIGHT JOIN b). En la semilla todas las claves foráneas casan, así que para ver NULL con FULL JOIN hay que unir por una condición que no siempre se cumpla (por ejemplo, empleados con pedidos: los 18 empleados sin pedidos aparecen a la izquierda con NULL, igual que en LEFT).

CROSS JOIN es la forma explícita del producto cartesiano. La forma implícita es FROM a, b (sin ON): produce lo mismo, pero CROSS JOIN deja clara la intención y evita confundirlo con un JOIN al que se le olvidó el ON.

SELECT e.nombre, COUNT(p.id) AS num_pedidos FROM empleados e LEFT JOIN pedidos p ON p.empleado_id = e.id GROUP BY e.id ORDER BY num_pedidos DESC LIMIT 6

Ejemplos

-- FULL OUTER JOIN: empleados y sus pedidos, conservando ambos lados.
-- Los 18 empleados sin pedidos aparecen con columnas de pedido en NULL.
SELECT e.nombre, p.id AS pedido, p.estado
FROM empleados e
FULL OUTER JOIN pedidos p ON p.empleado_id = e.id
ORDER BY e.nombre, p.id;

-- Reconciliación: solo las filas que NO casan (empleados sin pedido alguno)
SELECT e.nombre AS empleado, p.id AS pedido
FROM empleados e
FULL OUTER JOIN pedidos p ON p.empleado_id = e.id
WHERE p.id IS NULL OR e.id IS NULL
ORDER BY e.nombre;

-- OJO: los dos casos anteriores solo tienen huérfanas en UN lado (empleados sin
-- pedido), así que se comportan igual que un LEFT JOIN. Para ver de verdad por qué
-- FULL ≠ LEFT necesitamos huérfanas en AMBOS lados. El self-join del organigrama lo
-- demuestra: el CEO (id=1, jefe_id=0) no tiene jefe a un lado, y los empleados que no
-- mandan a nadie quedan sin subordinado al otro.

-- FULL OUTER JOIN con huérfanas en AMBOS lados (organigrama)
SELECT COALESCE(s.nombre, e.nombre) AS empleado,
       CASE WHEN s.id IS NULL THEN 'hoja (sin subordinados)' ELSE 'sin jefe (CEO)' END AS situacion
FROM empleados e
FULL OUTER JOIN empleados s ON s.jefe_id = e.id
WHERE s.id IS NULL OR e.id IS NULL;

-- CROSS JOIN: rejilla de todas las combinaciones departamento × categoría (6 × 6 = 36)
SELECT d.nombre AS departamento, c.nombre AS categoria
FROM departamentos d
CROSS JOIN categorias c
ORDER BY d.nombre, c.nombre;

💡 ¿FULL JOIN o LEFT JOIN? Si solo te interesan las filas no emparejadas de UN lado, basta LEFT JOIN ... IS NULL. El FULL JOIN es para cuando necesitas ver simultáneamente las huérfanas de AMBOS lados.

Cheatsheet

Forma	Qué hace
a FULL OUTER JOIN b ON ...	Todas las filas de ambas tablas (NULL donde falte pareja)
WHERE a.id IS NULL OR b.id IS NULL	Solo las filas no emparejadas de cualquier lado
a CROSS JOIN b	Producto cartesiano: n × m filas, sin ON
FROM a, b	Producto cartesiano implícito (equivale a CROSS JOIN)

---

← Conservar filas: LEFT y RIGHT JOINAuto-relaciones: SELF JOIN →

Ver todas las lecciones de Aprende SQL →