Um join monta linhas a partir de duas tabelas, combinadas por uma condição: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) mantém só as linhas que batem, LEFT JOIN mantém todas as linhas da primeira tabela e FULL JOIN todas as linhas das duas.
Voltam três linhas. Grace não tem pedidos, e o pedido 13 pertence a um cliente que não existe, então os dois ficam de fora. Os joins abaixo usam as mesmas duas tabelas.
Tipos de join
| Join | Linhas retornadas |
|---|---|
INNER JOIN | Os pares que atendem à condição |
LEFT JOIN | Todas as linhas da esquerda; colunas da direita em NULL onde nada bate |
RIGHT JOIN | Todas as linhas da direita; o espelho do LEFT JOIN |
FULL JOIN | Todas as linhas dos dois lados, com NULLs preenchendo as lacunas |
CROSS JOIN | Cada linha da esquerda combinada com cada linha da direita |
LEFT JOIN: manter linhas sem correspondência
Grace aparece com 0 pedidos. Conte o.id, não *: count(*) contaria a linha de NULLs da Grace como 1.
- Linhas sem correspondência (um anti join): adicione
WHERE o.id IS NULLpara ficar só com os clientes sem pedidos.WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)faz o mesmo e deixa a intenção clara. - ON vs WHERE: uma condição sobre a tabela da direita vai no
ON.LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20descarta a Grace, porque o totalNULLdela não passa noWHERE. O join virou um inner join. EscrevaON o.customer_id = c.id AND o.total > 20para manter todos os clientes.
FULL JOIN: linhas que faltam em qualquer um dos lados
O FULL JOIN acha divergências nas duas direções, o que ajuda ao comparar duas listas:
O resultado tem a Grace sem pedido e o pedido 13 sem cliente. Uma chave estrangeira em orders.customer_id teria impedido a segunda linha.
CROSS JOIN: todas as combinações
O CROSS JOIN não tem condição. Ele combina cada linha de um lado com cada linha do outro, então 3 tamanhos e 2 cores dão 6 linhas:
As duas listas aqui são listas VALUES com nome de tabela. Com tabelas reais fica FROM sizes CROSS JOIN colors, e FROM sizes, colors faz o mesmo. Um join cuja condição ON é sempre verdadeira multiplica as linhas do mesmo jeito, uma causa comum de consulta que de repente retorna linhas demais.
LATERAL: uma subconsulta para cada linha
Uma subconsulta no FROM normalmente não enxerga as outras tabelas do mesmo FROM. Com LATERAL ela enxerga, e é avaliada uma vez para cada linha da tabela antes dela. Isso resolve o "top N por grupo":
Ada recebe os dois maiores pedidos dela, Linus o único que tem, e Grace uma linha de NULLs por causa do LEFT JOIN ... ON true. CROSS JOIN LATERAL descartaria os clientes sem pedidos. Com um índice em orders (customer_id, total), cada subconsulta lê só algumas entradas do índice. Para uma linha por grupo, DISTINCT ON costuma ser mais curto.
Perguntas frequentes
JOIN é o mesmo que INNER JOIN no PostgreSQL?
É. JOIN sozinho significa INNER JOIN, e LEFT JOIN significa LEFT OUTER JOIN; as palavras INNER e OUTER são opcionais.
Por que meu LEFT JOIN retorna mais linhas que a tabela da esquerda?
Uma linha da esquerda aparece uma vez para cada linha da direita que bate. Um cliente com três pedidos aparece três vezes. Agregue com GROUP BY, ou faça o join com uma subconsulta que já tenha uma linha por chave.
Qual a diferença entre ON e USING?
ON aceita qualquer condição. USING (id) é um atalho para colunas iguais com o mesmo nome nas duas tabelas, e a saída tem uma única coluna id em vez de duas.
A ordem dos joins importa para o desempenho no PostgreSQL?
Normalmente não. O planejador reordena os inner joins para achar um plano barato quando a consulta tem até join_collapse_limit tabelas (8 por padrão). Os outer joins só podem ser reordenados em alguns casos, porque movê-los pode mudar o resultado.