El FULL OUTER JOIN completa la familia de los joins externos: conserva todas las filas de ambas tablas, casen o no. Es la unión de un LEFT JOIN y un RIGHT JOIN en una sola operación.

Es también, con diferencia, el JOIN que menos veces escribirás. Y por una razón interesante que esta lección explica: en una base de datos con integridad referencial bien declarada, como la de TiendaVerde, los huérfanos solo pueden existir en un lado. Un FULL OUTER JOIN entre dos tablas relacionadas por una clave foránea degenera casi siempre en un LEFT JOIN. Su verdadero terreno de juego está en otra parte: la conciliación de dos fuentes de datos independientes, donde ninguna de las dos manda sobre la otra.

1. La regla y el diagrama de conjuntos
2. FULL OUTER JOIN sobre TiendaVerde: por qué solo aparecen huérfanos en un lado
3. El caso real: conciliar dos fuentes de datos
4. El patrón "solo lo que no casa en ninguno de los dos lados"
5. Emulación en motores que no lo soportan
6. Soporte por motor y coste
7. Errores Comunes y Consejos
8. Ejercicios
9. Conclusión

El cuadro completo de la familia, ya con los cuatro tipos:

Sintaxis: FULL OUTER JOIN y FULL JOIN son lo mismo, porque OUTER es opcional igual que en LEFT y RIGHT. En este curso escribimos FULL OUTER JOIN completo, porque su rareza justifica ser explícito.

Y una propiedad que lo distingue de los otros dos externos: el FULL OUTER JOIN sí es conmutativo. A FULL OUTER JOIN B y B FULL OUTER JOIN A devuelven el mismo conjunto de filas, porque ambos lados reciben el mismo trato.

Probemos el caso más evidente: catálogo frente a ventas.

50 filas. Exactamente las mismas que devolvía el LEFT JOIN de 03-03:

El FULL OUTER JOIN no ha aportado ni una fila sobre el LEFT JOIN. ¿Por qué?

Porque para que apareciera un huérfano por el lado derecho tendría que existir una línea de pedido cuyo producto_id no correspondiera a ningún producto. Y eso es precisamente lo que la clave foránea impide:

Dos restricciones actúan a la vez:

De ahí sale una regla general muy útil:

Entre dos tablas unidas por una clave foránea NOT NULL con integridad referencial declarada, un FULL OUTER JOIN es siempre equivalente a un LEFT JOIN desde la tabla padre. Escribirlo es redundante, y además paga un coste que no necesita.

Lo mismo ocurre con clientes y pedidos: pedidos.cliente_id es NOT NULL REFERENCES clientes(id), así que clientes FULL OUTER JOIN pedidos devuelve las mismas 23 filas que el LEFT JOIN.

El único caso de TiendaVerde en que el FULL OUTER sí aportaría algo es pedidos con empleados, porque empleado_id admite NULL:

• Huérfanos por la izquierda: los 10 pedidos web sin empleado.
• Huérfanos por la derecha: los 5 empleados sin pedidos.
• Total: 10 emparejamientos + 10 + 5 = 25 filas.

Es el único FULL OUTER JOIN con sentido en este esquema, y aun así sería más claro escribir dos consultas separadas: "pedidos sin comercial" y "empleados sin pedidos" responden a preguntas de negocio distintas, y mezclarlas en una tabla con nulos por los dos lados no ayuda a nadie.

El FULL OUTER JOIN brilla cuando las dos tablas no están unidas por una clave foránea: cuando son dos fuentes independientes que deberían coincidir y hay que averiguar en qué no coinciden. Ejemplos habituales:

En todos ellos la clave es la misma: ninguna de las dos fuentes es la autoridad. Cada una puede tener filas que la otra no tiene, y el objetivo del informe es precisamente encontrarlas.

TiendaVerde ha empezado a vender también en un marketplace externo, que cada mes envía un fichero CSV con las unidades vendidas por SKU. Cargamos ese fichero en una tabla temporal:

Aviso: ventas_marketplace no forma parte del esquema de TiendaVerde. Es una tabla auxiliar creada solo para este ejemplo. No la uses en el resto de ejercicios del curso, y bórrala al terminar (o desconéctate: al ser TEMP, desaparece con la sesión).

19 filas. Fíjate en dos cosas: no hay ninguna clave foránea entre sku y productos.id —el fichero viene de otro sistema, no podemos imponerle restricciones—, y aparecen dos SKU extraños, el 101 y el 102.

22 filas = 17 emparejamientos + 3 productos sin ventas en el marketplace + 2 SKU desconocidos.

Ahora sí hay huérfanos por los dos lados, y cada uno cuenta una historia distinta:

• Los productos 13, 19 y 20 no se han vendido en el marketplace. Coinciden con los que tampoco se venden en la tienda propia, lo que refuerza el diagnóstico: sin stock, descatalogado y con problema comercial.
• Los SKU 101 y 102 han generado ventas en el marketplace pero no existen en el catálogo. Eso es una alerta operativa seria: puede ser una referencia antigua con otra numeración, un error de mapeo entre sistemas o ventas que nadie está imputando a ningún producto.

Un detalle de escritura: el ORDER BY COALESCE(p.id, vm.sku) ordena por el identificador "venga de donde venga". Si ordenaras solo por p.id, las dos filas del marketplace tendrían NULL en esa columna y se irían al final (o al principio, según NULLS FIRST/LAST, como viste en 02-05). COALESCE devuelve el primer valor no nulo de la lista, y se estudia a fondo en 06-04.

La conciliación completa está bien para revisar, pero lo que se envía a operaciones es solo la lista de discrepancias. Es el equivalente al anti-join de 03-03, ahora por partida doble:

5 filas: las tres discrepancias del catálogo y las dos del fichero. Esto es un informe de conciliación de verdad, y en un sistema real llegaría por correo cada mañana.

En términos de conjuntos, este patrón devuelve la diferencia simétrica:

Dos detalles importantes de escritura:

Y las tres variantes del filtro, según lo que quieras:

MySQL y MariaDB no implementan FULL OUTER JOIN. La emulación clásica consiste en combinar un LEFT JOIN y un RIGHT JOIN con UNION:

La lógica es directa:

Las 17 filas que casan aparecen en ambas consultas. Por eso hay que usar UNION y no UNION ALL: UNION elimina los duplicados y deja 20 + 19 − 17 = 22 filas. Con UNION ALL obtendrías 39 filas, con las 17 coincidencias repetidas dos veces.

UNION, UNION ALL y sus reglas son el tema de la lección 03-07; aquí basta con saber que existe la emulación y por qué necesita eliminar duplicados.

Nota de dialecto: en MySQL la emulación tiene una vuelta de tuerca desagradable. Como UNION compara filas completas, dos filas que difieran en cualquier columna se consideran distintas; si en tu conjunto de datos hubiera filas repetidas de forma legítima, UNION las colapsaría y perderías información. La alternativa robusta es hacer LEFT JOIN completo UNION ALL con solo los huérfanos de la derecha (RIGHT JOIN ... WHERE p.id IS NULL), que no genera solapamiento y no necesita deduplicar.

Cuatro razones, en orden de importancia:

1. La integridad referencial lo hace innecesario dentro de un mismo esquema. Es el argumento de la sección 2, y explica la mayoría de los casos.
2. Casi siempre lo que se quiere es una de las dos mitades. "Productos sin ventas" o "ventas sin producto" son preguntas concretas que un LEFT JOIN con anti-join responde mejor, y con un resultado más legible.
3. El resultado es incómodo de leer. Una tabla con nulos por los dos lados obliga a COALESCE para ordenar, para agrupar y para presentar. Cada columna clave existe por duplicado.
4. Es el JOIN más caro. El motor debe recorrer ambas relaciones por completo y marcar los no emparejados a los dos lados: no puede detenerse antes ni descartar pronto. En PostgreSQL solo se implementa con hash join o merge join; no existe un plan de nested loop para un FULL OUTER JOIN, lo que a veces obliga a materializar y ordenar ambos lados. Con tablas grandes se nota (módulo 8).

Regla práctica: antes de escribir un FULL OUTER JOIN, pregúntate si de verdad necesitas los huérfanos de ambos lados en el mismo resultado. Nueve de cada diez veces la respuesta es no, y un LEFT JOIN es más claro y más rápido. La décima vez —una conciliación real entre dos sistemas— es exactamente para lo que existe.

• Usar FULL OUTER JOIN entre dos tablas unidas por una FK NOT NULL. No aporta ninguna fila sobre el LEFT JOIN y cuesta más. La integridad referencial ya garantiza que no hay huérfanos del lado hijo.
• Escribir AND en lugar de OR en el filtro de discrepancias. WHERE p.id IS NULL AND vm.sku IS NULL devuelve siempre 0 filas: ninguna fila del resultado puede faltar por los dos lados a la vez.
• Ordenar por una sola de las dos claves. Las filas huérfanas del otro lado tienen esa columna a NULL y se van al extremo del listado. Usa ORDER BY COALESCE(a.clave, b.clave).
• Olvidar que las columnas clave están duplicadas. En la conciliación tienes p.id y vm.sku. Para presentar un identificador único hace falta COALESCE(p.id, vm.sku).
• Emularlo con UNION ALL en MySQL. Duplica todas las filas que casan: 39 en vez de 22 en el ejemplo de esta lección.
• Suponer que existe en MySQL. No existe, y no está previsto. Si tu SQL debe ser portable, evítalo.
• Confundir "sin pareja" con "dato vacío". Igual que en 03-03: comprueba siempre contra una columna que no pueda ser NULL legítimamente en su tabla de origen.
• Consejo: empieza por el LEFT JOIN y comprueba si necesitas más. Ejecuta el LEFT, cuenta filas, ejecuta el FULL OUTER y compara. Si el número no cambia, el FULL OUTER sobra.
• Consejo: en una conciliación, añade una columna que diga de dónde viene cada fila. Con CASE (módulo 6) puedes etiquetar cada fila como "solo catálogo", "solo fichero" o "coincide". El informe se vuelve autoexplicativo.
• Consejo: guarda el patrón de discrepancias. FULL OUTER JOIN ... WHERE a.clave IS NULL OR b.clave IS NULL es una plantilla que reutilizarás cada vez que dos sistemas deban cuadrar.

Ejecuta estas dos consultas y compara el número de filas:

1. ¿Cuántas filas devuelve cada una?
2. Explica el resultado en términos de las restricciones declaradas sobre pedidos.cliente_id.
3. ¿Qué tendría que cambiar en el esquema para que las dos consultas dieran resultados distintos?

Sobre la tabla temporal ventas_marketplace de la sección 3, escribe dos consultas separadas:

1. Los productos del catálogo que no aparecen en el fichero del marketplace, con su nombre, precio y stock.
2. Los SKU del fichero que no existen en el catálogo, con las unidades vendidas.

Escribe la primera con un LEFT JOIN y la segunda con un RIGHT JOIN, y después razona: ¿por qué en este caso resultan más útiles dos consultas separadas que el FULL OUTER JOIN de la sección 4?

Tu empresa migra a MySQL una consulta de conciliación escrita en PostgreSQL:

1. Reescríbela para MySQL usando UNION.
2. ¿Podrías usar UNION ALL en esta reescritura concreta? Razona la respuesta mirando qué devuelve cada mitad.

Solución 1

1. Ambas devuelven 23 filas.

2. La columna está declarada así:

Las tres partes actúan juntas:

• REFERENCES clientes(id) impide insertar un pedido con un cliente_id inexistente.
• NOT NULL impide insertar un pedido sin cliente.
• ON DELETE RESTRICT impide borrar un cliente que tenga pedidos, que es la otra forma de generar huérfanos.

Conclusión: no puede existir ningún pedido huérfano, así que el lado derecho no aporta nada y el FULL OUTER JOIN degenera en el LEFT JOIN. Las 23 filas son las mismas: 20 pedidos + 3 clientes sin pedidos.

3. Bastaría con que cliente_id admitiera NULL —por ejemplo, para registrar pedidos de invitados sin cuenta—. Esos pedidos no casarían con ningún cliente y aparecerían como huérfanos por la derecha, solo visibles con FULL OUTER JOIN (o con RIGHT JOIN). Es exactamente la situación de pedidos.empleado_id, que sí admite nulos: pedidos FULL OUTER JOIN empleados devuelve 25 filas frente a las 20 del LEFT JOIN.

Solución 2

1. Productos del catálogo que no están en el fichero:

2. SKU del fichero que no existen en el catálogo:

Por qué dos consultas separadas son más útiles aquí:

El FULL OUTER JOIN sigue siendo útil para la visión de conjunto —el cuadro de 22 filas de la sección 3, para revisar de un vistazo—, pero el trabajo operativo se despacha mejor con dos consultas dirigidas.

Solución 3

1. Reescritura para MySQL:

Devuelve las mismas 5 filas que la versión PostgreSQL.

2. ¿Se puede usar UNION ALL? Sí, y además es preferible.

El razonamiento es el que importa: UNION (sin ALL) hace falta cuando las dos mitades pueden producir las mismas filas, y hay que eliminar los duplicados. Ese es el caso de la emulación general de la sección 5, donde ambos JOIN devuelven las 17 filas que casan.

Aquí no ocurre. Cada mitad lleva su propio WHERE que la restringe a los huérfanos de su lado:

Ninguna fila puede cumplir las dos condiciones a la vez, así que los conjuntos son disjuntos y no hay nada que deduplicar. UNION ALL es más rápido porque se ahorra el paso de ordenación o de tabla hash que UNION necesita para detectar duplicados. Es un ejemplo perfecto de la regla que verás en 03-07: usa UNION ALL salvo que tengas una razón concreta para eliminar duplicados.

Cierras la familia de los joins externos:

• El FULL OUTER JOIN conserva las filas huérfanas de ambos lados: es un LEFT y un RIGHT a la vez. Es el único join externo conmutativo.
• Sobre TiendaVerde casi nunca aporta nada, y sabes explicar por qué: la integridad referencial (REFERENCES + NOT NULL + ON DELETE RESTRICT) impide que existan huérfanos del lado hijo. productos FULL OUTER JOIN lineas_pedido devuelve las mismas 50 filas que el LEFT JOIN.
• Su terreno real es la conciliación de dos fuentes independientes, donde ninguna manda sobre la otra: catálogo contra fichero de un marketplace, inventario contra contabilidad, extracto bancario contra facturas.
• Has construido esa conciliación completa (22 filas) y el informe de discrepancias con el patrón WHERE a.clave IS NULL OR b.clave IS NULL (5 filas): tres productos que el marketplace no vende y dos SKU que no existen en el catálogo. Recuerda: el operador es OR, nunca AND.
• Sabes emularlo con UNION de un LEFT JOIN y un RIGHT JOIN para MySQL, y por qué esa emulación necesita eliminar duplicados salvo que cada mitad esté restringida a sus propios huérfanos.
• Y conoces su coste: no admite plan de nested loop, obliga a recorrer ambas relaciones enteras y produce un resultado que necesita COALESCE para casi todo. Úsalo cuando de verdad necesites los dos lados.

En la lección siguiente, SELF JOIN y CROSS JOIN, veremos los dos JOIN "raros" que más confunden a quien empieza. El SELF JOIN te permitirá recorrer por fin las dos relaciones reflexivas de TiendaVerde: la jerarquía de empleados con jefe_id —donde volverás a necesitar un LEFT JOIN para no perder a Rosa, la directora sin jefe— y la red de referidos de clientes. El CROSS JOIN, que hasta ahora solo ha aparecido como accidente, se convertirá en una herramienta deliberada para generar combinaciones completas.