SQL FULL OUTER JOIN: conciliar datos y emularlo en MySQL

FULL OUTER JOIN es el join que no descarta nada. Un INNER JOIN devuelve solo las coincidencias, un LEFT JOIN protege la tabla izquierda y un RIGHT JOIN protege la derecha. Un FULL OUTER JOIN conserva las filas de ambos lados: cuando coinciden, las dos mitades se cosen juntas; cuando no, la mitad que falta se rellena con NULL. Por eso mismo brilla en la conciliación: cuando necesitas encontrar las diferencias entre dos fuentes, y no su intersección.

En PostgreSQL es un operador nativo. MySQL no tiene nada parecido, así que hay que montarlo a mano. Veámoslo sobre un esquema users / orders.

Qué devuelve realmente FULL OUTER JOIN

Supongamos que tenemos usuarios registrados y pedidos. Algunos pedidos los hicieron invitados (sin user_id) y algunos usuarios todavía no han comprado nada.

-- PostgreSQL
SELECT
    u.id        AS user_id,
    u.email,
    o.id        AS order_id,
    o.amount
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

El resultado se divide en tres grupos lógicos:

• Coincidencias — el usuario tiene un pedido; ambas mitades de la fila están pobladas.
• Solo izquierda — un usuario sin pedidos: las columnas o.* son NULL.
• Solo derecha — un pedido sin usuario (un invitado o un user_id colgado): las columnas u.* son NULL.

La palabra clave OUTER es opcional: FULL JOIN y FULL OUTER JOIN son idénticos. El predicado del join sigue viviendo en ON, y es simétrico: intercambiar el orden de las tablas no cambia el conjunto de resultados (solo el orden de las columnas).

Conciliación: a la caza de discrepancias

La verdadera ventaja práctica de FULL OUTER JOIN es sacar a la luz todo lo que existe en un solo lado. El caso clásico: conciliar los pagos de un sistema de facturación contra los pedidos de nuestra base de datos y ver de un vistazo los cabos sueltos de ambos lados.

-- Orders with no payment AND payments with no order, in one query
SELECT
    o.id          AS order_id,
    o.amount      AS order_amount,
    p.id          AS payment_id,
    p.amount      AS payment_amount,
    CASE
        WHEN o.id IS NULL THEN 'payment without order'
        WHEN p.id IS NULL THEN 'order without payment'
        WHEN o.amount <> p.amount THEN 'amount mismatch'
        ELSE 'ok'
    END AS status
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id
WHERE o.id IS NULL
   OR p.id IS NULL
   OR o.amount <> p.amount;

El filtro o.id IS NULL OR p.id IS NULL conserva solo las filas sin coincidencia: ese es tu informe de discrepancias. Para encontrar verdaderos huérfanos en cualquiera de los dos lados, comprueba NULL sobre una columna que no pueda ser nula por sí misma, como la clave primaria, en lugar de un campo que legítimamente podría ser NULL en los datos.

Otra consulta habitual es medir la magnitud del estropicio:

SELECT
    count(*) FILTER (WHERE o.id IS NULL) AS orphan_payments,
    count(*) FILTER (WHERE p.id IS NULL) AS unpaid_orders
FROM orders o
FULL OUTER JOIN payments p ON p.order_id = o.id;

Emularlo en MySQL: LEFT UNION RIGHT

MySQL no admite FULL OUTER JOIN — la consulta falla con un error de sintaxis. El truco estándar es combinar un LEFT JOIN y un RIGHT JOIN con UNION.

-- MySQL: emulating FULL OUTER JOIN
SELECT u.id AS user_id, u.email, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id

UNION

SELECT u.id AS user_id, u.email, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

Cómo funciona: el LEFT JOIN aporta todas las coincidencias más los usuarios sin pedidos; el RIGHT JOIN aporta esas mismas coincidencias más los pedidos sin usuario. UNION suelda los dos conjuntos y elimina los duplicados (las filas coincidentes que cayeron en ambas mitades).

• Usa UNION, no UNION ALL: UNION es lo que elimina los duplicados de las filas coincidentes. UNION ALL emitiría cada coincidencia dos veces.
• Ambas mitades deben tener las mismas columnas en el mismo orden.

UNION tiene una sutileza: deduplica considerando todas las columnas a la vez. Si tus datos contienen filas duplicadas «legítimas» totalmente idénticas, UNION también las colapsará. Cuando eso importa, escribe la segunda consulta como un LEFT JOIN que excluya las coincidencias ya encontradas y usa UNION ALL:

-- Alternative without implicit deduplication
SELECT u.id, u.email, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id

UNION ALL

SELECT u.id, u.email, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id
WHERE u.id IS NULL;   -- only rows the LEFT half didn't already cover

Errores frecuentes

• NULL en el predicado del join. JOIN ... ON a.col = b.col nunca coincide sobre NULL — NULL = NULL evalúa a UNKNOWN. Esas filas caen en la parte sin coincidencia de un FULL JOIN. Si quieres que NULL coincida con NULL, usa ON a.col IS NOT DISTINCT FROM b.col (PostgreSQL).
• No confundas «sin coincidencia» con «el valor es NULL». Tras un FULL JOIN, una columna puede ser NULL por dos motivos: o no había fila coincidente, o el valor de origen era genuinamente NULL. La única forma fiable de distinguirlos es una columna no nullable — normalmente la clave primaria.
• WHERE frente a ON. Un predicado en ON controla qué cuenta como coincidencia; un predicado en WHERE se ejecuta después del join y convierte silenciosamente un FULL JOIN en uno INNER. Escribe WHERE o.status = 'paid' y desaparece toda fila donde o.* IS NULL (los usuarios sin pedidos).
• ClickHouse. Aquí FULL JOIN sí se admite, pero las columnas sin coincidencia toman los valores por defecto del tipo (0, cadena vacía) en lugar de NULL. Para obtener NULL honestos, envuelve las columnas en Nullable o establece join_use_nulls = 1.

FULL OUTER JOIN es la herramienta del «muéstrame todo y resalta lo que falta». PostgreSQL te lo da de serie, MySQL necesita LEFT más RIGHT pegados con UNION, y ClickHouse exige cuidado con los NULL. Ten presente la diferencia entre «sin coincidencia» y «el valor es NULL», y la conciliación de datos deja de ser un suplicio.