Rode cada join SQL em duas pequenas tabelas de exemplo direto no navegador e veja quais linhas sobram, com a sintaxe do PostgreSQL para INNER, LEFT, RIGHT, FULL, CROSS e self joins.
Um join combina linhas de duas tabelas usando uma condição, geralmente chaves correspondentes. INNER JOIN mantém só as linhas que correspondem dos dois lados. LEFT JOIN mantém todas as linhas da tabela da esquerda e preenche com NULL as colunas da direita que faltam, RIGHT JOIN faz o inverso, FULL JOIN mantém as dos dois lados e CROSS JOIN combina cada linha com todas as outras.
Quais linhas cada join mantém
Join
Linhas correspondentes
Linhas da esquerda sem correspondência
Linhas da direita sem correspondência
INNER JOIN
Sim
Não
Não
LEFT JOIN
Sim
Sim, lado direito NULL
Não
RIGHT JOIN
Sim
Não
Sim, lado esquerdo NULL
FULL JOIN
Sim
Sim
Sim
CROSS JOIN
Todos os pares, sem condição
Diagramas de Venn são a forma usual de ensinar joins, mas escondem a parte que realmente pega: linhas duplicadas e NULLs. Abaixo há duas tabelas pequenas, customers e orders, com um cliente que nunca fez pedido e um pedido cujo cliente foi apagado. Escolha um join e o resultado é calculado no seu navegador, para você ver exatamente quais linhas aparecem e por quê.
Leia as duas tabelas de exemplo. Ana tem dois pedidos, Ben tem um, Cleo não tem nenhum, e o pedido 104 aponta para um id de cliente que não existe.
Escolha um tipo de join. A consulta e a tabela de resultado são atualizadas na hora.
As linhas que o join preencheu com NULL ficam destacadas, para você ver onde os outer joins adicionaram linhas.
Copie a consulta e teste em qualquer banco PostgreSQL com o script de preparação mais abaixo.
Coloque as condições sobre a tabela da direita de um LEFT JOIN na cláusula ON, não no WHERE. Um filtro WHERE nessas colunas descarta as linhas com NULL e transforma, sem avisar, o join em um inner join.
Para encontrar linhas sem correspondência, faça um LEFT JOIN e depois filtre com WHERE right_table.id IS NULL.
Conte as linhas antes e depois de um join. Se o número crescer sem você esperar, a chave do join não é única em um dos lados.
Prefira USING (column) ou um ON explícito em vez de NATURAL JOIN, que passa a comparar silenciosamente qualquer coluna nova que por acaso tenha o mesmo nome.
Perguntas frequentes
Qual é a diferença entre INNER JOIN e LEFT JOIN?
INNER JOIN descarta as linhas que não têm correspondência. LEFT JOIN mantém todas as linhas da primeira tabela e coloca NULL nas colunas da segunda quando nada corresponde. Na demonstração, Cleo aparece no LEFT JOIN com um pedido vazio e não aparece no INNER JOIN.
JOIN é o mesmo que INNER JOIN?
Sim. No PostgreSQL, a palavra INNER é opcional e um JOIN sozinho é um inner join. A palavra OUTER também é opcional, então LEFT JOIN e LEFT OUTER JOIN significam a mesma coisa.
Por que meu join retornou mais linhas do que a tabela tem?
Cada linha da esquerda aparece uma vez para cada linha correspondente da direita. Um cliente com três pedidos aparece três vezes. Esse é o comportamento correto; agregue com GROUP BY ou faça o join com uma subconsulta já agrupada se precisar de uma linha por cliente.
O que é um self join?
Uma tabela unida a ela mesma com dois aliases, usada quando as linhas apontam para outras linhas da mesma tabela, como funcionários e seus gerentes ou clientes e quem os indicou.
O MySQL suporta FULL OUTER JOIN?
O PostgreSQL suporta, e essa é a sintaxe mostrada aqui. O MySQL não tem a palavra-chave FULL JOIN; a alternativa usual é combinar um LEFT JOIN com um RIGHT JOIN usando UNION.
ON, USING e NATURAL
ON aceita qualquer condição, então lida com nomes de coluna diferentes e regras extras, como um intervalo de datas. USING (customer_id) é um atalho para quando as duas tabelas dão o mesmo nome à coluna, e mostra essa coluna uma vez em vez de duas.
No PostgreSQL, FROM a, b é o mesmo que um CROSS JOIN, e uma cláusula WHERE o transforma em um inner join. A sintaxe explícita de JOIN é mais clara e tem precedência maior quando você mistura os dois estilos.
Filtros de outer join rodam em outra ordem
As condições no ON são aplicadas enquanto as linhas estão sendo combinadas, antes de qualquer preenchimento com NULL. As condições no WHERE rodam sobre o resultado final. Em inner joins as duas dão a mesma resposta; em outer joins podem dar respostas bem diferentes.
Teste no PostgreSQL
Cole isto no psql ou em qualquer editor SQL do Postgres para recriar as tabelas de exemplo e depois rode as consultas da demonstração.
Tabelas de exemplo
O pedido 104 aponta de propósito para o cliente 9, que não existe, para que os outer joins tenham algo a mostrar.
Clientes que nunca fizeram pedido
O LEFT JOIN mantém Cleo com as colunas de pedido em NULL, e o filtro IS NULL mantém só essas linhas.
Playground de joins
Tabelas de exemplo usadas na demonstração
Tipo de join
Resultado: {n} linhas
As células destacadas foram preenchidas com NULL pelo outer join.