Junções SQL e relacionamentos entre tabelas

O endereço de um cliente pertence aos dados do cliente. A data de um pedido pertence aos dados do pedido. Manter esses dados em tabelas separadas reduz a repetição, mas um relatório frequentemente precisa de dados de ambas. Uma junção combina suas linhas dentro de uma consulta.

Leia as chaves

Uma chave primária identifica uma linha. Ela pode ser uma coluna ou várias colunas juntas. Uma chave estrangeira se refere a uma chave primária ou outra chave única, em outra tabela ou na mesma tabela. No banco de dados da loja, orders.customer_id aponta para customers.customer_id.

Os pedidos 101 e 102 se referem ao cliente 1, Ana. O pedido 103 se refere a Ben. O pedido 104 é uma compra sem cadastro: sua chave de cliente é NULL, então não se refere a nenhum cliente. O número do pedido identifica o pedido; ele não identifica o cliente. Juntar order_id a customer_id compararia identificadores sem relação entre si.

Classifique o relacionamento

O relacionamento cliente–pedido é um para muitos. Um cliente pode não ter pedidos, ter um pedido ou vários. Isso não significa que a tabela de clientes armazene um identificador de pedido para cada compra. Cada pedido armazena sua própria referência ao cliente.

Para alunos e cursos, nenhum dos lados pode conter apenas uma referência para descrever todos os vínculos. Uma linha de enrollments registra um par aluno–curso. Várias linhas de matrícula podem se referir ao mesmo aluno, e várias podem se referir ao mesmo curso.

Trace um caminho antes de escrever SQL

Para listar os produtos comprados por cada cliente, siga customers → orders → order_items → products. Os vínculos usam customer_id, depois order_id e depois product_id. Cada vínculo precisa de sua própria condição. Um item do pedido fornece a conexão que falta entre um pedido e um produto.

Leia o significado do esquema, não apenas a grafia das colunas. employees.manager_id pode se referir a employees.emp_id, embora os nomes sejam diferentes. Por outro lado, customers.name e products.name nomeiam tipos diferentes de coisas e não devem ser relacionados apenas porque ambos se chamam name.

Uma chave estrangeira declarada é uma orientação útil, mas SQL também pode juntar tabelas sem uma restrição declarada. Um preço pode corresponder a uma faixa de preço, e dois funcionários podem corresponder porque têm o mesmo gerente. A condição deve expressar o relacionamento necessário para a pergunta.

Uma junção cria novas linhas combinando linhas de entrada. Comece pela regra de correspondência e depois pergunte quantos correspondentes cada linha tem. A resposta determina quais linhas aparecem e com que frequência seus valores se repetem.

Pense em pares

Para uma junção interna, imagine cada cliente ao lado de cada pedido. Mantenha um par apenas quando o.customer_id = c.customer_id for verdadeiro. O pedido 101 forma um par com Ana; ele não forma um par com Ben, Chloe ou Dev. O pedido 102 também forma um par com Ana. Um cliente pode, portanto, contribuir com várias linhas de resultado.

A condição pode ser verdadeira, falsa ou desconhecida. Uma igualdade que envolve NULL é desconhecida, então o pedido sem cadastro 104 não encontra um cliente correspondente. Uma junção externa também preserva linhas sem correspondência do lado escolhido e preenche as colunas do correspondente ausente com NULL.

Essa representação par a par explica as junções melhor que círculos sobrepostos. Uma junção não apenas reúne itens compartilhados: ela constrói linhas combinadas, e chaves repetidas podem criar mais linhas de saída do que qualquer uma das entradas contém. Se duas linhas de um lado e três do outro compartilham uma chave, essa chave produz seis pares correspondentes.

Escolha colunas independentemente das correspondências

A tabela de clientes da loja tem três colunas e a tabela de pedidos tem quatro. Com JOIN ... ON, SELECT * retorna todas as sete, incluindo as duas cópias de customer_id. Você pode selecionar apenas os campos necessários para o relatório:

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

O resultado é (Ana; 101; 22,00), (Ana; 102; 25,50) e (Ben; 103; 60,00). As colunas de chave fazem seu trabalho de correspondência sem precisar aparecer na saída selecionada. Selecionar menos colunas altera a largura do resultado, não quais pares de entrada correspondem.

Dê nomes curtos e claros às tabelas

Em FROM customers c, c é um apelido de tabela. Refira-se às colunas dessa tabela como c.name e c.customer_id. A expressão c.name é um nome de coluna qualificado: a parte antes do ponto indica qual papel de tabela fornece o valor.

As duas tabelas da junção contêm customer_id. Escrever apenas SELECT customer_id com a forma ON deixa o banco de dados sem saber qual delas escolher e causa um erro de referência ambígua a coluna. Escreva c.customer_id ou o.customer_id de acordo com o valor necessário. Qualificar outras colunas selecionadas também facilita a identificação de sua origem.

AS antes de um apelido de tabela é opcional no PostgreSQL, SQLite, MySQL e SQL Server. O Oracle não o aceita, então FROM customers c é uma forma útil para todos eles. Depois de dar um apelido a uma tabela, use-o de forma consistente.

Leia a ordem lógica da consulta

FROM e suas junções criam as linhas combinadas. WHERE filtra essas linhas. SELECT escolhe as colunas de saída e ORDER BY ordena o resultado. Um filtro pode usar uma coluna de uma tabela da junção mesmo quando essa coluna não é selecionada. Por exemplo, WHERE c.city = 'Denver' pode filtrar um relatório que exibe apenas nomes e totais de pedidos.

Sem ORDER BY, o banco de dados não garante nenhuma ordem específica das linhas. Uma consulta que exibe Ana primeiro hoje pode exibir outra linha primeiro depois; a ordenação é uma instrução separada da correspondência.

Use uma junção interna quando uma linha de resultado precisar de uma correspondência nas duas tabelas. JOIN e INNER JOIN significam a mesma coisa. Nenhum dos dois preserva uma linha que não encontra correspondente.

Monte a consulta em três partes

Primeiro, nomeie as tabelas e seus papéis. Depois, declare o relacionamento em ON. Por fim, escolha as colunas a exibir.

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Leia a condição como “a referência ao cliente no pedido é igual ao identificador deste cliente”. Para o pedido 101, a referência é 1, então ele forma um par com Ana. O pedido 102 também tem referência 1, e o pedido 103 tem referência 2. O resultado completo é:

Ana aparece duas vezes porque tem dois pedidos correspondentes. Chloe e Dev não têm nenhum, então não aparecem. O pedido sem cadastro 104 tem uma referência de cliente NULL, que não satisfaz nenhuma igualdade com uma chave de cliente.

Filtre e ordene as linhas combinadas

Para mostrar apenas clientes de Denver, coloque WHERE c.city = 'Denver' após a junção e antes de ORDER BY. O resultado passa a ser (Ana; 101; 22,00) e (Ana; 102; 25,50). Chloe também mora em Denver, mas a junção interna nunca produziu uma linha para ela.

Em uma junção interna, colocar esse filtro em ON produz os mesmos pares. Uma convenção que facilita a leitura é manter as condições de correspondência entre tabelas em ON e os filtros do relatório em WHERE. Essa equivalência não se estende às junções externas.

Você pode ordenar por uma coluna de uma tabela da junção que não aparece na saída selecionada. Também pode omitir as chaves de junção da saída: a consulta acima faz a correspondência por customer_id enquanto exibe um nome, um número de pedido e um total. O papel de uma coluna na correspondência é separado de seu papel no relatório.

Compare todas as partes de uma chave

Alguns registros são identificados por um par de colunas. Um item de remessa que se refere a um item do pedido pode precisar dos identificadores tanto do pedido quanto do produto:

SELECT s.order_id, s.product_id, oi.quantity
FROM shipment_lines s
JOIN order_items oi
  ON s.order_id = oi.order_id
 AND s.product_id = oi.product_id;

As duas comparações devem ser verdadeiras para haver correspondência. Comparar apenas order_id associaria cada item de remessa a cada item de produto desse pedido. A consulta poderia ser executada com sucesso enquanto descreve as quantidades de produtos erradas.

Altere a ordem sem alterar o significado

Escrever FROM orders o JOIN customers c ON c.customer_id = o.customer_id produz os mesmos pares cliente–pedido. Inverter os dois lados de = também preserva uma igualdade. SELECT * pode organizar as colunas de forma diferente após a troca das tabelas, e a ordem das linhas continua indefinida a menos que sejam ordenadas.

Uma alteração muito mais séria é ON o.order_id = c.customer_id. As duas colunas contêm números, então isso é SQL válido, mas elas identificam coisas diferentes. Nos dados da loja, a consulta retorna zero linhas: os identificadores de pedido 101–104 nunca são iguais aos identificadores de cliente 1–4. Verifique o relacionamento no esquema mesmo quando o banco de dados não informa erro de sintaxe.

ON torna uma condição explícita, mas códigos SQL existentes podem usar formas mais curtas ou antigas. Leia cada forma perguntando quais pares ela mantém. Algumas formas também alteram como as colunas compartilhadas aparecem na saída.

USING nomeia a chave compartilhada

Quando as colunas de chave têm o mesmo nome, esta consulta usa uma igualdade abreviada:

SELECT c.name, o.order_id
FROM customers c
JOIN orders o USING (customer_id)
ORDER BY o.order_id;

Ela retorna (Ana, 101), (Ana, 102) e (Ben, 103). USING (customer_id) significa comparar as duas colunas customer_id. Com SELECT *, retorna seis colunas, mostrando a chave compartilhada uma vez; JOIN ... ON inclui as duas colunas de chave das entradas.

Para uma chave composta, liste os dois nomes: USING (order_id, product_id). Cada coluna listada deve existir com esse nome nas duas entradas.

PostgreSQL, SQLite, MySQL e Oracle oferecem suporte a USING; SQL Server não. No Oracle, uma coluna nomeada em USING deve ser referenciada sem qualificação, então escreva SELECT customer_id, não SELECT c.customer_id. Outras colunas ainda podem usar seus apelidos de tabela.

NATURAL JOIN escolhe colunas para você

Um NATURAL JOIN compara todos os nomes de coluna compartilhados pelas entradas, combina esses testes de igualdade com AND e mostra as colunas compartilhadas uma vez. PostgreSQL, SQLite, MySQL e Oracle oferecem suporte a ele, mas SQL Server não.

Essa escolha automática pode alterar uma consulta sem aviso. Clientes e pedidos atualmente compartilham apenas customer_id. Adicionar uma coluna city a pedidos faz uma junção natural exigir correspondência tanto do identificador do cliente quanto da cidade. Uma cidade de entrega diferente da cidade do cliente pode então remover um pedido que antes tinha correspondência.

Clientes e produtos têm name, mas um nomeia uma pessoa e o outro nomeia um produto. A junção natural deles compararia esses nomes sem relação e não retornaria linhas nos dados da loja. Sem nomes de coluna compartilhados, uma junção natural retorna todas as combinações. Use ON ou uma lista USING intencional para manter a regra de correspondência visível.

Leia junções antigas por vírgulas

SELECT c.name, o.order_id
FROM customers c, orders o
WHERE o.customer_id = c.customer_id;

Isso retorna os mesmos três pares que a junção interna. A condição agora fica em WHERE. Se você a esquecer, todos os quatro clientes formarão pares com todos os quatro pedidos, produzindo 16 linhas. JOIN ... ON mantém cada tabela ao lado de sua própria condição, o que facilita a verificação de consultas mais longas.

Relacione um valor a um intervalo

Uma equijunção usa igualdade. Uma junção por desigualdade usa outra comparação, como um teste de intervalo:

SELECT p.name, b.band
FROM products p
JOIN price_bands b
  ON p.price BETWEEN b.min_price AND b.max_price;

As faixas são Budget 0,00–4,99, Standard 5,00–19,99 e Premium 20,00–999,99. Mug por 8,00 e Stapler por 12,00 correspondem a Standard; Pen por 1,50 e Notebook por 4,25 correspondem a Budget. Premium não tem produto correspondente e não contribui com nenhuma linha.

BETWEEN inclui os dois extremos. Uma nova faixa Gift de 8,00 a 15,00 daria a Mug e Stapler uma segunda correspondência para cada um. A lacuna entre 4,99 e 5,00 deixaria um preço de 4,995 sem correspondência. Os intervalos determinam o resultado assim como as chaves: um valor pode ter zero, uma ou várias correspondências.

Um relatório que pede “todos os clientes, incluindo clientes sem pedidos” precisa de uma junção externa à esquerda. LEFT JOIN preserva cada linha à esquerda. Ele associa cada linha correspondente à direita ou fornece uma linha de valores NULL quando não há correspondência. A palavra OUTER é opcional.

Acompanhe a tabela preservada

SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Ana ainda fornece duas linhas. Uma junção à esquerda não reduz os pedidos de um cliente a um. Chloe e Dev fornecem exatamente uma linha cada, pois nenhum dos dois tem correspondência. Há cinco linhas no total: três pares correspondentes e dois clientes sem correspondência.

A junção retorna pelo menos tantas linhas quanto sua entrada à esquerda antes de filtros posteriores. Para cada linha à esquerda, conte suas correspondências; se não houver nenhuma, conte uma linha preenchida com NULL. Isso explica por que quatro clientes podem produzir cinco linhas.

O pedido sem cadastro 104 está ausente. Preservar todos os clientes não preserva também os pedidos sem correspondência. Para preservar todos os pedidos, comece com orders o LEFT JOIN customers c ON c.customer_id = o.customer_id. Selecionar o.order_id, c.name produz então (101, Ana), (102, Ana), (103, Ben) e (104, NULL). Chloe e Dev não aparecem mais.

Encontre linhas sem correspondente

Uma antijunção à esquerda mantém apenas as linhas que não encontraram correspondência:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A junção à esquerda primeiro cria todas as cinco linhas. WHERE mantém então Chloe e Dev, cujas chaves de pedido são NULL. O correspondente ausente é exatamente o que a consulta procura.

Escolha uma coluna do lado direito que não possa ser NULL em uma correspondência real. A chave primária de um pedido é um bom teste. Testar um coupon_code opcional também manteria pedidos correspondentes reais sem cupom, então isso não significaria “nenhum pedido”. Use IS NULL: = NULL é avaliado como desconhecido e não mantém nenhuma linha em WHERE.

O mesmo padrão encontra produtos não vendidos:

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

Ele retorna (Stapler), o único produto sem item de pedido. Aqui uma linha correspondente real necessariamente tem um identificador de pedido, então o teste detecta a linha ausente.

Interprete um valor ausente antes de substituí-lo

Uma linha de junção externa preenchida com NULL significa que nenhum correspondente foi encontrado. Um NULL armazenado em um pedido real pode, por sua vez, significar que um campo não foi fornecido. Essas são situações diferentes, embora ambas sejam exibidas como NULL.

COALESCE(o.total, 0) escolhe o primeiro argumento que não é NULL. Ele exibiria 0 para Chloe e Dev. Isso pode ser útil para um relatório de gastos, mas um zero em uma coluna chamada “total do pedido” pode sugerir um pedido real de valor zero. Mantenha o rótulo e o significado do relatório alinhados.

Às vezes, as linhas necessárias estão à direita de uma junção. Em outras situações, as duas entradas devem ser preservadas para que registros ausentes possam ser comparados. Junções externas à direita e completas tornam essas escolhas explícitas.

Reescreva uma junção à direita como junção à esquerda

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

Isso preserva todos os pedidos: (Ana, 101), (Ana, 102), (Ben, 103) e (NULL, 104). A tabela à direita é orders, então o pedido sem cadastro permanece. Chloe e Dev são linhas à esquerda sem correspondência e não permanecem.

Trocar as entradas produz a forma equivalente orders o LEFT JOIN customers c ON o.customer_id = c.customer_id. Manter a mesma lista explícita de seleção produz os mesmos valores. Com SELECT *, a ordem padrão das colunas pode mudar. Usar junções à esquerda em todo o relatório frequentemente facilita acompanhar o lado que deve ser preservado.

Preserve linhas sem correspondência das duas entradas

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

O resultado contém (Ana, 101), (Ana, 102), (Ben, 103), (Chloe, NULL), (Dev, NULL) e (NULL, 104). São três pares correspondentes, dois clientes sem correspondência e um pedido sem correspondência: seis linhas. As colunas de cliente são NULL para o pedido sem cadastro 104; as colunas de pedido são NULL para Chloe e Dev.

Uma junção completa é útil para a conciliação, como verificar uma lista de estoque de armazém em relação a uma contagem física. Chaves correspondentes aparecem juntas; chaves encontradas em apenas um lado permanecem visíveis. Na consulta da loja, adicionar WHERE c.customer_id IS NULL OR o.order_id IS NULL mantém apenas Chloe, Dev e o pedido 104.

Ao conciliar duas listas do mesmo tipo de chave, qualquer uma das chaves de entrada pode ser NULL na saída. COALESCE(w.sku, k.sku) AS sku seleciona o SKU disponível do apelido w do armazém ou do apelido k da lista de contagem. Uma coluna de chave combinada permite ao relatório identificar registros ausentes de qualquer uma das listas.

Verifique o suporte à sintaxe

PostgreSQL, SQL Server e Oracle oferecem suporte a junções externas à direita e completas. SQLite oferece suporte a ambas a partir da versão 3.39.0, lançada em 2022. Versões anteriores do SQLite as rejeitam. MySQL oferece suporte a junções à direita, mas não tem FULL OUTER JOIN; uma solução alternativa combina resultados de junções à esquerda e à direita com UNION.

Códigos SQL antigos do Oracle podem marcar o lado opcional com (+) em WHERE. Por exemplo, WHERE c.customer_id = o.customer_id(+) preserva os clientes e preenche colunas de pedido ausentes com NULL, como uma junção à esquerda a partir de clientes. O marcador pertence ao lado que pode estar ausente. Use as formas de junção nomeadas LEFT, RIGHT e FULL ao escrever novas consultas.

Uma junção externa separa duas decisões: quais pares contam como correspondências e quais linhas de resultado concluídas o relatório mantém. ON toma a primeira decisão. WHERE toma a segunda, depois que as linhas sem correspondência recebem valores NULL.

Preserve todos, mas limite seus pedidos correspondentes

Suponha que um relatório precise de todos os clientes e apenas seus pedidos a partir de 10 de março:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-03-10';

O teste de data faz parte da correspondência. O pedido 101 de Ana é anterior ao limite, mas o pedido 102 atende à condição. O pedido 103 de Ben atende à condição. Chloe e Dev não têm correspondente que atenda à condição, então cada um recebe uma linha preenchida com NULL. O resultado é (Ana, 102), (Ben, 103), (Chloe, NULL) e (Dev, NULL).

Filtre as linhas após a junção

Mova o mesmo teste de data para WHERE:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-10';

Agora a junção cria primeiro cinco linhas, incluindo as linhas preenchidas com NULL para Chloe e Dev. A comparação de data é desconhecida para esses valores NULL, então WHERE as remove. Ele também remove o pedido anterior de Ana. Restam apenas (Ana, 102) e (Ben, 103).

A versão com WHERE produz as mesmas linhas correspondentes que uma junção interna com esse filtro de data.

Filtre a tabela preservada intencionalmente

Uma condição do lado esquerdo em ON também não pode remover linhas à esquerda. Considere ON o.customer_id = c.customer_id AND c.city = 'Denver'. Ana pode corresponder aos dois pedidos, Chloe não tem nenhum, e Ben e Dev não atendem à parte da condição referente à cidade em nenhuma correspondência possível. Todos os quatro clientes permanecem, com colunas de pedido NULL para Ben, Chloe e Dev.

Para solicitar apenas clientes de Denver, coloque WHERE c.city = 'Denver' após a condição normal de chave. O resultado é (Ana, 101), (Ana, 102) e (Chloe, NULL). Chloe permanece porque o filtro testa sua própria cidade, não um campo de pedido ausente.

O teste de antijunção pertence à etapa após a junção porque examina o resultado preenchido com NULL. Junções internas não têm linhas sem correspondência preservadas, então mover um filtro entre ON e WHERE não altera seus pares correspondentes. Ao revisar uma junção externa, leia cada teste WHERE do lado opcional e verifique o que ele faz com uma linha preenchida com NULL.

O gerente de um funcionário também é funcionário. Uma autojunção lê a mesma tabela com apelidos diferentes para que uma linha possa representar o funcionário e outra possa representar o gerente. Ela usa a sintaxe comum de junção; não existe uma palavra-chave SELF JOIN.

Nomeie os papéis antes das colunas

Os dados dos funcionários são:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

e fornece a linha do funcionário. Seu manager_id aponta para o emp_id do gerente no papel m. Hassan aponta para 1, então forma um par com Grace; Iris também forma um par com Grace. Jamal aponta para 2 e forma um par com Hassan. O resultado é (Hassan, Grace), (Iris, Grace) e (Jamal, Hassan).

Grace não tem gerente. Seu manager_id é NULL, então a junção interna a exclui. Substituir JOIN por LEFT JOIN adiciona (Grace, NULL), produzindo quatro linhas. Apelidos distintos permitem que o banco de dados diferencie os papéis, embora as duas entradas venham da mesma tabela armazenada.

Faça a pergunta no outro sentido

Para encontrar pessoas que não gerenciam ninguém, comece pelo papel de gerente e procure um funcionário ausente:

SELECT m.name
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
WHERE e.emp_id IS NULL;

Isso retorna (Iris) e (Jamal). Grace corresponde a Hassan e Iris; Hassan corresponde a Jamal. Iris e Jamal não têm subordinados diretos. Testar a chave do funcionário após a junção encontra o correspondente ausente deles.

Um terceiro apelido segue mais um nível. employees e JOIN employees m ON e.manager_id = m.emp_id JOIN employees g ON m.manager_id = g.emp_id, selecionando seus nomes, retorna (Jamal, Hassan, Grace). Cada papel adicional segue mais um vínculo. Uma consulta fixa alcança um número fixo de níveis; uma hierarquia de profundidade arbitrária precisa de uma consulta recursiva.

Forme pares de colegas uma vez

A mesma tabela também pode fornecer pares de colegas de trabalho:

SELECT a.name, b.name
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.emp_id < b.emp_id;

Hassan e Iris compartilham o gerente 1. O teste de menor que exclui uma pessoa emparelhada consigo mesma e mantém apenas uma ordem de cada par. O resultado é (Hassan, Iris).

Apenas com a igualdade de gerente, o resultado tem cinco linhas: Hassan–Hassan, Hassan–Iris, Iris–Hassan, Iris–Iris e Jamal–Jamal. A chave de gerente NULL de Grace não corresponde a nada, incluindo outro NULL. Substituir < por <> exclui pares de uma pessoa consigo mesma, mas mantém tanto (Hassan, Iris) quanto (Iris, Hassan). Escolha < quando a pergunta precisar de cada par não ordenado uma vez.

Uma junção cruzada cria todos os pares de linhas, sem uma condição de correspondência. Isso é útil quando a pergunta pede possibilidades, como todas as combinações de tamanho–cor que uma loja poderia ter em estoque.

Crie a lista de combinações

sizes contém S, M e L. colors contém Red e Blue.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Os seis pares são (S, Red), (S, Blue), (M, Red), (M, Blue), (L, Red) e (L, Blue). Cada tamanho aparece ao lado das duas cores. Essas são as linhas de resultado; sua ordem de exibição não é garantida sem ORDER BY.

Para duas entradas, multiplique suas contagens de linhas. Três tamanhos e duas cores produzem seis linhas. Com uma terceira tabela contendo quatro opções, o resultado tem 24 linhas. Se qualquer entrada estiver vazia, nenhuma combinação completa pode ser formada e o resultado tem zero linhas.

Comece com uma grade completa para relatório

Os registros de vendas mostram apenas combinações que ocorreram. Para mostrar todas as lojas em todos os meses, primeiro forme uma grade com stores CROSS JOIN months. Depois, use uma junção à esquerda para associar os registros de vendas correspondentes:

SELECT s.store_id, m.month, x.total
FROM stores s
CROSS JOIN months m
LEFT JOIN sales x
  ON x.store_id = s.store_id
 AND x.month = m.month;

A grade preserva um par loja–mês mesmo quando não há venda, com NULL em x.total. As duas partes da chave são importantes: comparar apenas a loja também associaria as vendas dessa loja em outros meses. A junção cruzada define quais combinações o relatório deve mostrar, e a junção à esquerda fornece os dados disponíveis sobre elas.

Identifique uma junção cruzada não intencional

FROM customers, orders sem condição de junção forma 16 pares a partir dos quatro clientes e dos quatro pedidos. Ana forma pares com seus próprios pedidos, com o pedido de Ben e com o pedido sem cadastro. Cada cliente faz o mesmo. Isso só é útil se todas as combinações cliente–pedido forem realmente desejadas.

Uma contagem alta de linhas é um indício, mas examine também os pares. Com 1.000 clientes e 1.000 produtos, uma junção cruzada intencional produz 1.000.000 de linhas. Uma junção com uma condição errada, mas seletiva, também pode retornar um resultado menor e incorreto. Compare a contagem e o significado de cada par com a pergunta.

Reconheça formas equivalentes

CROSS JOIN, uma junção por vírgulas sem condição de correspondência e uma junção interna com ON 1 = 1 mantêm todos os pares. SQLite e MySQL também aceitam um JOIN simples sem ON e o tratam como junção cruzada; PostgreSQL exige ON ou USING para uma junção simples. Escreva CROSS JOIN quando todas as combinações forem intencionais.

DISTINCT remove linhas selecionadas idênticas, não relacionamentos incorretos. Se uma junção cruzada acidental emparelhar Ana com o pedido de Ben, esses valores permanecerão como uma linha distinta. Corrija a condição ausente em vez de ocultar parte da saída repetida.

Um aluno pode fazer vários cursos, e um curso pode ter vários alunos. Uma tabela associativa registra esses vínculos muitos para muitos como pares individuais. Ela também é chamada de tabela de junção ou de ligação.

Leia um vínculo por linha

Os dados de exemplo contêm os alunos 1 Mia, 2 Noah e 3 Omar; os cursos 10 Algebra, 20 Biology e 30 Chemistry; e os pares de matrícula (1, 10), (1, 20) e (2, 10).

O aluno 1 aparece duas vezes porque Mia faz dois cursos. O curso 10 aparece duas vezes porque Algebra tem dois alunos. Nenhuma das colunas precisa ser única por si só. O par pode formar uma chave primária composta, PRIMARY KEY (student_id, course_id), impedindo que o mesmo aluno seja matriculado no mesmo curso duas vezes.

Um vínculo pode ter dados próprios. enrolled_on pertence à matrícula porque descreve quando um determinado aluno ingressou em um determinado curso. Da mesma forma, order_items.quantity descreve a quantidade de um determinado produto que pertence a um determinado pedido.

Atravesse a tabela associativa com duas junções

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id;

A primeira condição associa as linhas de matrícula de cada aluno. A segunda substitui a referência ao curso de cada matrícula por dados da tabela de cursos. O resultado completo é (Mia, Algebra), (Mia, Biology) e (Noah, Algebra). Cada linha representa uma matrícula.

Não há uma chave que relacione aluno e curso em nenhuma das tabelas das extremidades. Comparar s.student_id = c.course_id compararia identificadores sem relação e ignoraria os dados de matrícula. A tabela associativa fornece o relacionamento.

O mesmo caminho funciona nos dois sentidos. Adicione WHERE c.title = 'Algebra' para obter (Mia, Algebra) e (Noah, Algebra). Adicione WHERE s.name = 'Mia' em vez disso para obter (Mia, Algebra) e (Mia, Biology). Os vínculos permanecem os mesmos; o filtro seleciona quais vínculos respondem à pergunta.

Inclua pessoas ou cursos sem vínculos

Comece pelo lado que deve ser preservado. students s LEFT JOIN enrollments e ON e.student_id = s.student_id preserva Omar com colunas de matrícula NULL. Para mostrar os títulos dos cursos e preservá-lo, a junção posterior de matrículas a cursos também deve preservar a matrícula ausente.

Para encontrar cursos que ninguém faz, use o lado dos cursos e uma antijunção:

SELECT c.title
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
WHERE e.student_id IS NULL;

Isso retorna (Chemistry). Em uma matrícula correspondente real, student_id faz parte da chave primária e não pode ser NULL, então o teste identifica o vínculo ausente.

Armazene um relacionamento por linha da tabela associativa. Uma lista de texto como “10,20” em um campo do aluno não pode ser comparada por igualdade aos identificadores individuais de curso 10 e 20. Pares separados permitem que cada condição de junção compare uma referência com uma chave.

Uma junção mais longa segue a mesma regra de uma junção de duas tabelas: associe a próxima tabela usando uma condição que faça sentido. Antes de escrever a consulta, siga as chaves e decida o que uma linha de resultado deve representar.

Adicione os dados detalhados da loja

Os itens de pedido (order_id, product_id, quantity) são (101, 1, 2), (101, 2, 4), (102, 3, 6), (103, 1, 6), (103, 2, 8) e (104, 2, 10). Cada item vincula um pedido a um produto e registra uma quantidade.

Para a pergunta “O que cada cliente comprou?”, siga customers → orders → order_items → products. As chaves no caminho são identificador do cliente, identificador do pedido e identificador do produto. Quatro tabelas nessa cadeia exigem três vínculos.

Associe uma nova tabela por vez

SELECT c.name, o.order_id, p.name AS product,
       oi.quantity, oi.quantity * p.price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_id;

Cada ON se refere à tabela recém-introduzida e a uma que já está disponível. Os produtos são associados por meio dos itens de pedido, não diretamente por meio dos clientes. O resultado é:

A primeira junção exclui o pedido sem cadastro 104 porque sua referência ao cliente é NULL. Os três pedidos restantes contêm cinco itens, então o resultado final tem cinco linhas. Para o pedido 101, duas canecas custam 16,00 e quatro canetas custam 6,00, correspondendo ao total do pedido de 22,00.

Saiba o que uma linha conta

Uma linha de resultado representa um item do pedido. Ana e o pedido 101 aparecem duas vezes porque esse pedido tem dois produtos. Portanto, nomes de clientes repetidos são esperados. Contar essas linhas conta itens, não clientes ou pedidos distintos.

Um filtro pode se referir a qualquer tabela da junção. Adicionar WHERE p.name = 'Pen' antes de ORDER BY mantém (Ana; 101; Pen; 4; 6,00) e (Ben; 103; Pen; 8; 12,00). Nenhum filtro de produto pode recuperar o pedido sem cadastro que já foi excluído no vínculo com clientes.

Mantenha cada vínculo visível

Em uma cadeia de junções internas, a ordem escrita das tabelas pode mudar sem alterar as combinações correspondentes, desde que cada condição se refira apenas a tabelas já introduzidas. Começar pelo assunto do relatório e seguir suas chaves geralmente facilita a leitura.

Uma condição ausente pode multiplicar um resultado intermediário no SQLite ou MySQL, que aceitam um JOIN simples sem ON. PostgreSQL e SQL Server rejeitam essa sintaxe. Uma condição ausente em uma consulta com vírgulas também pode criar todas as combinações nessa etapa.

Às vezes, uma tabela desempenha dois papéis. Um relatório de voos pode juntar airports como dep por dep.code = f.from_code e novamente como arr por arr.code = f.to_code. Apelidos distintos dão aos dados de partida e chegada posições separadas no resultado.

Uma junção à esquerda preserva linhas em uma etapa. Junções e filtros posteriores ainda atuam sobre o resultado dessa etapa. Para preservar uma linha pai sem correspondência em um caminho mais longo, acompanhe o que acontece com suas colunas preenchidas com NULL em cada estágio.

Veja onde uma linha preservada desaparece

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id;

A primeira etapa cria cinco linhas: os dois pedidos de Ana, o pedido de Ben e uma linha preenchida com NULL para Chloe e outra para Dev. Na etapa seguinte, os valores o.order_id de Chloe e Dev são NULL. Eles não podem satisfazer a igualdade com uma chave de item de pedido, então a junção interna os remove. A consulta retorna cinco linhas de itens de pedido e perde os dois clientes sem pedidos.

Preserve o caminho opcional

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

Agora a segunda junção também preserva essas linhas preenchidas com NULL. O resultado tem (Ana, 101, 1), (Ana, 101, 2), (Ana, 102, 3), (Ben, 103, 1), (Ben, 103, 2), (Chloe, NULL, NULL) e (Dev, NULL, NULL): sete linhas.

Quando junções posteriores dependem de colunas de uma tabela opcional, geralmente precisam continuar sendo junções à esquerda para preservar os correspondentes ausentes. Uma junção interna posterior em colunas de uma tabela obrigatória tem outro papel, então examine a dependência real de cada condição.

Siga a mesma regra por uma tabela associativa

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
LEFT JOIN courses c ON c.course_id = e.course_id;

Isso retorna (Mia, Algebra), (Mia, Biology), (Noah, Algebra) e (Omar, NULL). Tornar a última junção interna remove Omar porque sua matrícula ausente não pode corresponder a um curso.

Os parênteses oferecem outra forma de expressar o agrupamento: students s LEFT JOIN (enrollments e JOIN courses c ON c.course_id = e.course_id) ON e.student_id = s.student_id. As correspondências matrícula–curso formam o grupo opcional; a junção externa associa esse grupo a cada aluno, preservando Omar.

Verifique filtros posteriores e a direção da junção

Adicionar WHERE c.title = 'Algebra' à consulta de alunos que usa apenas junções à esquerda deixa apenas Mia e Noah em Algebra. A comparação é desconhecida para o título de curso NULL de Omar. Isso responde a “Quem faz Algebra?”, mas não responde mais a “Mostre todos os alunos e seus cursos”. Um teste IS NULL se comporta de forma diferente porque aceita intencionalmente um valor ausente.

Uma junção à direita posterior muda novamente o lado preservado: ela preserva a tabela à direita recém-adicionada, e linhas anteriores sem correspondência podem desaparecer. Coloque primeiro a tabela que deve ser preservada e associe caminhos opcionais com junções à esquerda quando isso expressar o relatório com clareza.

Construa e examine a consulta uma junção por vez. Anote tanto a contagem de linhas quanto as chaves que permanecem. Uma contagem em queda pode revelar linhas sem correspondência perdidas; um salto inesperado pode revelar uma parte ausente da chave. Algumas contagens permanecem inalteradas quando linhas são perdidas e multiplicadas ao mesmo tempo, então examine também chaves representativas.

Uma junção pode estar correta enquanto uma agregação sobre seu resultado está errada. A questão principal é o que uma linha representa após a junção. Um valor armazenado uma vez por pedido pode aparecer várias vezes em uma lista de itens de pedido.

Acompanhe um total de pedido repetido

Ana tem o pedido 101 de 22,00 e o pedido 102 de 25,50. Somar os dois pedidos dá 47,50. O pedido 101 tem dois itens, então a junção com order_items repete 22,00 duas vezes; o pedido 102 tem um item. Somar os valores repetidos dá 69,50.

SELECT c.customer_id, c.name, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

GROUP BY reúne as linhas de cada cliente, e SUM soma os valores desse grupo. Essa consulta retorna (1; Ana; 47,50) e (2; Ben; 60,00). Adicionar a junção com itens de pedido antes do agrupamento inflaria o valor de Ana para 69,50 e o de Ben para 120,00. Os totais dos dois clientes passariam a 189,50 em vez de 107,50.

Nos quatro pedidos, incluindo o pedido sem cadastro 104, juntar pedidos diretamente a seus itens faz SUM(o.total) retornar 204,50 em vez de 122,50. Essa multiplicação de linhas vem do vínculo um para muitos, não de um erro de sintaxe.

Some os dados no nível em que estão armazenados

Para totais de pedidos, some os pedidos sem adicionar a tabela de detalhes. Para um total de valores de itens, calcule o valor de cada item:

SELECT SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;

Os seis valores dos itens são 16,00, 6,00, 25,50, 48,00, 12,00 e 15,00. Eles somam 122,50, o total dos quatro pedidos. Cada produto de quantidade por preço pertence a um item, então cada um é somado uma vez.

Conte correspondências reais

Após juntar clientes a pedidos e itens, Ana ocupa três linhas. COUNT(*) conta esses itens; COUNT(DISTINCT o.order_id) conta seus dois pedidos distintos.

Após apenas uma junção à esquerda de clientes a pedidos, COUNT(*) conta uma linha preenchida com NULL para Chloe e uma para Dev. Em vez disso, conte a chave opcional do pedido:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

Isso retorna (1, Ana, 2), (2, Ben, 1), (3, Chloe, 0) e (4, Dev, 0). COUNT(column) ignora valores NULL; COUNT(*) conta linhas.

Dois lados muitos se multiplicam

Se um cliente tem três pedidos e dois chamados, juntar as duas tabelas filhas independentemente a esse cliente cria seis linhas. Cada pedido forma um par com cada chamado. Contagens simples de qualquer uma das chaves filhas passam a seis. Conte chaves filhas distintas para obter três e dois ou totalize as tabelas filhas separadamente.

DISTINCT c.customer_id, c.name é útil para uma lista de clientes sem duplicatas. Manter a chave evita unir clientes diferentes que têm o mesmo nome. Isso não corrige uma condição de junção ausente. SUM(DISTINCT o.total) também não é seguro: ele une dois pedidos separados que por acaso custam o mesmo valor.

Uma consulta pode ser executada e ainda assim descrever os relacionamentos errados. Antes de usar um resultado de junção, preveja o que deve permanecer, com que frequência cada chave deve aparecer e o que uma linha de resultado deve significar.

Faça uma previsão da contagem de linhas

Se a referência ao cliente em um pedido corresponde a uma chave única de cliente, cada pedido tem no máximo um cliente correspondente. Portanto, uma junção interna retorna no máximo uma linha por pedido. Nos dados da loja, três dos quatro pedidos têm um cliente, então a junção interna cliente–pedido retorna três linhas.

Do lado dos clientes, uma junção à esquerda retorna os três pares correspondentes mais uma linha para Chloe e uma para Dev: cinco linhas. Uma junção cruzada dos quatro clientes com os quatro pedidos produz 16. Essas previsões fornecem verificações diferentes para tipos diferentes de junção; o número de tabelas sozinho não determina a contagem.

Investigue a forma de um resultado errado

Comparar apenas store_id quando os registros são identificados por (store_id, month) mistura meses diferentes. Compare cada componente com AND. Uma consulta que por acaso funciona enquanto uma loja tem um mês pode falhar assim que um segundo mês for carregado.

Separe valores ausentes de chaves ruins

Com igualdade comum, chaves NULL não correspondem, incluindo NULL com NULL. Juntar pedidos a eles mesmos por customer_id produz quatro pares dos dois pedidos de Ana e um do pedido de Ben, totalizando cinco linhas. O pedido sem cadastro não contribui com nenhuma. Uma junção externa é necessária quando uma linha com referência ausente precisa ser preservada.

Valores de consulta duplicados multiplicam apenas as correspondências desses valores. Se um código de país ocorre duas vezes em uma tabela de consulta, cada cliente com esse código recebe duas linhas correspondentes. Códigos que ocorrem uma vez ainda produzem um correspondente. Examine uma tabela de consulta que deveria ser única com:

SELECT code, COUNT(*) AS copies
FROM countries
GROUP BY code
HAVING COUNT(*) > 1;

GROUP BY reúne as linhas de cada código; HAVING mantém grupos cuja contagem é maior que um. Carregar a tabela de consulta inteira duas vezes duplicaria as linhas de cada cliente correspondente.

Examine também os tipos e formatos das chaves. O texto “0042” e o número 42 podem produzir comportamentos diferentes entre bancos de dados: nenhuma correspondência, um erro de tipo ou uma conversão automática. O SQLite pode relacionar uma coluna TEXT contendo “0042” a uma coluna INTEGER contendo 42. Use tipos e formatos de chave consistentes ou uma conversão explícita escolhida para o significado pretendido.

Verifique os requisitos do relatório

Pergunte qual tabela deve preservar todas as linhas. Siga seu caminho completo de chaves e verifique cada condição em relação ao esquema. Decida por que cada vínculo é interno ou externo e depois verifique se cada filtro pertence à etapa de correspondência ou à etapa posterior. Compare as contagens de linhas após cada etapa e examine uma linha correspondente conhecida, uma linha sem correspondência e uma linha com vários correspondentes. Por fim, verifique se cada contagem e soma mede o tipo de registro pretendido.