SQL FULL OUTER JOIN: conservar todas las filas de ambas tablas

Lección 5.5 · Tiempo de lectura: ~6 min

SQL FULL OUTER JOIN devuelve todas las filas de ambas tablas: las filas coincidentes se combinan, y las filas sin coincidencia de cualquiera de los lados se conservan con NULL en las columnas de la otra tabla. En esta lección aprenderás la sintaxis, qué bases de datos la admiten y cómo obtener el mismo resultado en MySQL y MariaDB.

LEFT JOIN conserva las filas sin coincidencia de una tabla, RIGHT JOIN las de la otra. FULL OUTER JOIN conserva ambas. Es la herramienta para comparar dos listas: qué está en las dos, qué está solo en la primera y qué está solo en la segunda.

Qué devuelve FULL OUTER JOIN

El resultado tiene tres partes:

1. Pares de filas coincidentes, como en INNER JOIN.
2. Filas de la tabla izquierda sin coincidencia, con NULL en las columnas de la derecha.
3. Filas de la tabla derecha sin coincidencia, con NULL en las columnas de la izquierda.

Sintaxis básica

SELECT left_table.column1, right_table.column2
FROM left_table
FULL OUTER JOIN right_table ON left_table.key_column = right_table.key_column;

FULL JOIN y FULL OUTER JOIN son lo mismo.

Ejemplo: actores y clientes

¿Quién en la base Sakila es actor, quién es cliente y quién es las dos cosas? En PostgreSQL o SQL Server, un join completo por nombre lo responde directamente:

-- PostgreSQL, SQL Server
SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name;

Cada actor aparece una vez, cada cliente aparece una vez, y una persona que es ambas cosas aparece en una sola fila con los dos lados completos.

FULL OUTER JOIN en MySQL y MariaDB

MySQL y MariaDB no tienen FULL OUTER JOIN, pero el mismo resultado se puede armar con dos consultas unidas por UNION ALL:

1. Un LEFT JOIN devuelve las coincidencias y las filas que están solo en la tabla izquierda.
2. Un RIGHT JOIN con WHERE left_key IS NULL agrega solo las filas que están solo en la tabla derecha.

SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
LEFT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
UNION ALL
SELECT a.first_name, a.last_name, c.first_name, c.last_name
FROM actor AS a
RIGHT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL;

Resultado: 798 filas: 200 actores más 599 clientes, menos una persona que es ambas cosas y aparece en una sola fila combinada (JENNIFER DAVIS).

A veces esta alternativa se escribe con UNION en lugar de UNION ALL y sin la condición WHERE. Solo funciona si el resultado no tiene filas que se repitan legítimamente, porque UNION elimina todos los duplicados, y además es más lenta. La versión con UNION ALL es exacta. Aprenderás UNION y UNION ALL en la lección 5.10.

Usar un join completo para comparar listas

Un join completo es útil siempre que dos fuentes deban contener los mismos elementos:

• conciliar los pagos de la base de datos con un extracto bancario;
• comparar un catálogo de productos con la lista de stock;
• encontrar registros que existen solo en uno de dos sistemas después de una migración.

Para quedarte solo con las diferencias, filtra las filas en las que uno de los lados es NULL:

-- PostgreSQL, SQL Server: personas que son solo actores o solo clientes
SELECT a.actor_id, c.customer_id
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL OR c.customer_id IS NULL;

Conclusiones clave:

• FULL OUTER JOIN devuelve las filas coincidentes más las filas sin coincidencia de ambas tablas.
• Las columnas del lado que falta son NULL.
• PostgreSQL, SQL Server, Oracle y SQLite 3.39+ lo admiten; MySQL y MariaDB no.
• En MySQL y MariaDB: LEFT JOIN UNION ALL RIGHT JOIN ... WHERE left_key IS NULL.
• Un join completo es la herramienta natural para comparar dos listas.

Preguntas frecuentes

¿MySQL admite FULL OUTER JOIN?

No. MySQL y MariaDB no lo admiten. Combina un LEFT JOIN y un RIGHT JOIN con UNION ALL, conservando en la segunda consulta solo las filas sin coincidencia (WHERE left_key IS NULL).

¿Cuál es la diferencia entre FULL OUTER JOIN y CROSS JOIN?

FULL OUTER JOIN empareja filas según una condición y conserva las filas sin coincidencia de ambas tablas. CROSS JOIN no tiene condición y devuelve todas las combinaciones de filas, por lo que su resultado suele ser mucho más grande.

¿Por qué usar UNION ALL en lugar de UNION para emular FULL OUTER JOIN?

UNION elimina filas duplicadas, incluidas las que se repiten legítimamente en los datos, y necesita trabajo extra para encontrar duplicados. UNION ALL con el filtro IS NULL en la segunda consulta devuelve exactamente las filas de un join completo.