JOINs

Um JOIN combina linhas de duas tabelas por uma condição. O tipo de JOIN decide o que fazer com a linha que não encontra par.

INNER JOIN: só quem tem par

O pedido 109 é de um cliente_id que não existe; ele some do INNER JOIN.

SELECT p.id, c.nome, p.valor
FROM pedidos p
INNER JOIN clientes c ON c.id = p.cliente_id
WHERE p.data = '2024-06-05';

id  | nome | valor
--- | ---- | -----
110 | Ana  | 900

LEFT JOIN: todos da esquerda

Todo cliente aparece; quem não comprou vem com NULL nas colunas do pedido.

SELECT c.nome, p.id AS pedido
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE c.id IN (5, 6);

nome  | pedido
----- | ------
Elisa | 107
Fábio | NULL

O filtro no lugar errado vira INNER

Condição sobre a tabela da direita no WHERE descarta as linhas com NULL e desfaz o LEFT JOIN. Se o filtro é parte do "casar", ele vai no ON.

SELECT 'no WHERE' AS onde, COUNT(DISTINCT c.id) AS clientes
FROM clientes c LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.status = 'entregue'
UNION ALL
SELECT 'no ON', COUNT(DISTINCT c.id)
FROM clientes c LEFT JOIN pedidos p ON p.cliente_id = c.id AND p.status = 'entregue';

onde     | clientes
-------- | --------
no WHERE | 5
no ON    | 6

Anti-join: quem não tem par

O jeito mais claro de achar órfãos é NOT EXISTS. Aqui, os pedidos sem cliente (um problema de qualidade de dados) e os clientes sem pedido.

SELECT 'pedido sem cliente' AS caso, p.id
FROM pedidos p
WHERE NOT EXISTS (SELECT 1 FROM clientes c WHERE c.id = p.cliente_id)
UNION ALL
SELECT 'cliente sem pedido', c.id
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

caso               | id
------------------ | ---
pedido sem cliente | 109
cliente sem pedido | 6

JOIN que multiplica linhas

Se a chave não é única do lado de lá, cada linha casa com várias. Somar depois de um JOIN assim conta o mesmo valor duas vezes: o pedido 101 tem dois itens, então o valor dele aparece duas vezes.

SELECT SUM(p.valor) AS somado_certo,
       (SELECT SUM(p2.valor) FROM pedidos p2 JOIN itens i ON i.pedido_id = p2.id WHERE p2.id = 101) AS somado_apos_join
FROM pedidos p
WHERE p.id = 101;

somado_certo | somado_apos_join
------------ | ----------------
230          | 460

• No Spark, o anti-join tem nome próprio: LEFT ANTI JOIN; e o semi-join, LEFT SEMI JOIN.
• Antes de juntar, confira a cardinalidade: COUNT(*) contra COUNT(DISTINCT chave) do lado de lá.