SQL FULL OUTER JOIN: reconciliar dados e simular no MySQL

FULL OUTER JOIN é o join que não joga nada fora. Um INNER JOIN retorna apenas as correspondências, um LEFT JOIN protege a tabela da esquerda e um RIGHT JOIN protege a da direita. Um FULL OUTER JOIN mantém as linhas dos dois lados: quando elas correspondem, as duas metades são costuradas juntas; quando não, a metade que falta é preenchida com NULL. É justamente por isso que ele brilha na reconciliação — quando você precisa encontrar as diferenças entre duas fontes, e não a interseção delas.

No PostgreSQL é um operador nativo. O MySQL não tem nada parecido, então você precisa montá-lo na mão. Vamos percorrer isso sobre um esquema users / orders.

O que o FULL OUTER JOIN realmente retorna

Digamos que temos usuários cadastrados e pedidos. Alguns pedidos foram feitos por visitantes (sem user_id) e alguns usuários ainda não compraram 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;

O resultado se divide em três grupos lógicos:

• Correspondências — o usuário tem um pedido; as duas metades da linha estão preenchidas.
• Só à esquerda — um usuário sem pedidos: as colunas o.* são NULL.
• Só à direita — um pedido sem usuário (um visitante ou um user_id solto): as colunas u.* são NULL.

A palavra-chave OUTER é opcional: FULL JOIN e FULL OUTER JOIN são idênticos. O predicado do join continua morando no ON, e ele é simétrico — inverter a ordem das tabelas não altera o conjunto de resultados (apenas a ordem das colunas).

Reconciliação: caçando divergências

O verdadeiro ganho prático do FULL OUTER JOIN é trazer à tona tudo o que existe em apenas um lado. O caso clássico: reconciliar os pagamentos de um sistema de cobrança contra os pedidos do nosso banco de dados e ver as pontas soltas dos dois lados de uma vez.

-- 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;

O filtro o.id IS NULL OR p.id IS NULL mantém apenas as linhas sem correspondência — esse é o seu relatório de divergências. Para encontrar órfãos de verdade em qualquer um dos lados, teste NULL em uma coluna que não possa ser nula por si só, como a chave primária, em vez de um campo que legitimamente poderia ser NULL nos dados.

Outra consulta comum é dimensionar o estrago:

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;

Simulando no MySQL: LEFT UNION RIGHT

O MySQL não suporta FULL OUTER JOIN — a consulta falha com um erro de sintaxe. O truque padrão é combinar um LEFT JOIN e um RIGHT JOIN com 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;

Como funciona: o LEFT JOIN traz todas as correspondências mais os usuários sem pedidos; o RIGHT JOIN traz essas mesmas correspondências mais os pedidos sem usuário. O UNION solda os dois conjuntos e remove as duplicatas (as linhas correspondentes que caíram nas duas metades).

• Use UNION, não UNION ALL: é o UNION que elimina as duplicatas das linhas correspondentes. O UNION ALL emitiria cada correspondência duas vezes.
• As duas metades precisam ter as mesmas colunas na mesma ordem.

Há uma sutileza no UNION: ele deduplica considerando todas as colunas de uma vez. Se os seus dados contêm linhas duplicadas «legítimas» totalmente idênticas, o UNION também vai colapsá-las. Quando isso importa, escreva a segunda consulta como um LEFT JOIN que exclua as correspondências já encontradas e use 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

Armadilhas

• NULL no predicado do join. JOIN ... ON a.col = b.col nunca corresponde sobre NULL — NULL = NULL avalia como UNKNOWN. Essas linhas caem na parte sem correspondência de um FULL JOIN. Se você quer que NULL corresponda a NULL, use ON a.col IS NOT DISTINCT FROM b.col (PostgreSQL).
• Não confunda «sem correspondência» com «o valor é NULL». Depois de um FULL JOIN, uma coluna pode ser NULL por dois motivos: ou não havia linha correspondente, ou o valor de origem era genuinamente NULL. A única forma confiável de distinguir os dois é uma coluna não nullable — normalmente a chave primária.
• WHERE versus ON. Um predicado no ON controla o que conta como correspondência; um predicado no WHERE roda depois do join e transforma silenciosamente um FULL JOIN em um INNER. Escreva WHERE o.status = 'paid' e toda linha em que o.* IS NULL (usuários sem pedidos) desaparece.
• ClickHouse. Aqui o FULL JOIN é suportado, mas as colunas sem correspondência assumem os valores padrão do tipo (0, string vazia) em vez de NULL. Para obter NULL honestos, envolva as colunas em Nullable ou defina join_use_nulls = 1.

FULL OUTER JOIN é a ferramenta do «mostre-me tudo e destaque o que está faltando». O PostgreSQL te entrega isso de fábrica, o MySQL precisa de LEFT mais RIGHT colados com UNION, e o ClickHouse exige cuidado com os NULL. Tenha em mente a diferença entre «sem correspondência» e «o valor é NULL», e a reconciliação de dados deixa de ser um suplício.