PG Módulo 6: JOIN, subconsultas e CTE

Antes de começar

JOIN no Postgres é o mesmo da Trilha 1. Três atalhos que aparecem muito:

JOIN produtos USING (produto_id): quando a coluna de ligação tem o mesmo nome nas duas tabelas, USING (col) substitui o ON a.col = b.col (e a coluna aparece uma vez só no resultado).
LEFT JOIN … WHERE b.id IS NULL: o padrão "quem não tem par": clientes sem pedido, produtos nunca vendidos.
WITH nome AS (…) (CTE): dá nome a uma consulta e usa como se fosse tabela. Deixa a query em etapas legíveis.
EXISTS (SELECT 1 FROM … WHERE …): "existe pelo menos uma linha relacionada". NOT EXISTS pro contrário. Costuma ser mais claro (e rápido) que IN (subquery).

Dois exemplos

Teoria

a JOIN b ON b.a_id = a.id: só as linhas que casam nos dois lados.
a JOIN b USING (col): quando o nome da coluna é igual nos dois.
a LEFT JOIN b ON …: mantém tudo de a; o que não casa vem NULL em b.
WITH x AS (SELECT …) SELECT … FROM x …: CTE, consulta nomeada.
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id AND …): tem relacionada?
WHERE col IN (SELECT … ): valor dentro do resultado de outra query.

Exemplos de aplicação

Um exemplo montado pra cada item da teoria acima. Não precisa rodar, é pra ver a cara do recurso e entender cada pedaço. Os valores são ilustrativos.

`JOIN ... ON`

SELECT p.id, p.valor_total, c.nome AS cliente
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id;

O ON diz como as tabelas se casam: o cliente_id do pedido é o id de clientes. Só entram as linhas que casam nos dois lados (JOIN = INNER JOIN).

`JOIN ... USING (col)`

-- quando a coluna de ligação tem o MESMO nome nos dois lados:
SELECT ip.pedido_id, ip.quantidade, pr.nome
FROM itens_pedido ip
JOIN produtos pr ON pr.id = ip.produto_id;
-- se a FK em itens_pedido se chamasse "id" igual em produtos,
-- daria pra escrever:  JOIN produtos USING (id)

USING (col) é atalho de ON a.col = b.col pra quando o nome é idêntico nos dois lados, e ainda funde a coluna repetida numa só no resultado. Neste banco as chaves têm nomes diferentes (produto_id ↔ id), então o ON é o que se usa, mas a sintaxe é essa.

`LEFT JOIN`

SELECT c.nome, COUNT(p.id) AS qtd_pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.nome
ORDER BY qtd_pedidos;

LEFT JOIN mantém todo cliente, mesmo sem pedido: nesse caso as colunas de p vêm NULL. Como COUNT(p.id) ignora NULL, clientes sem pedido aparecem com 0 (em vez de sumirem, como num JOIN normal).

`WITH x AS (...)` (CTE)

WITH gasto_por_cliente AS (
  SELECT cliente_id, SUM(valor_total) AS total
  FROM pedidos
  WHERE status <> 'cancelado'
  GROUP BY cliente_id
)
SELECT c.nome, g.total
FROM gasto_por_cliente g
JOIN clientes c ON c.id = g.cliente_id
WHERE g.total > 10000;

A CTE calcula o gasto por cliente e ganha um nome; a query de baixo usa gasto_por_cliente como se fosse tabela. Deixa cada etapa separada e legível, melhor que aninhar subconsulta dentro de subconsulta.

`WHERE EXISTS (...)`

SELECT c.nome
FROM clientes c
WHERE EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.cliente_id = c.id
    AND p.status = 'cancelado'
);

Pra cada cliente, testa se existe ao menos um pedido cancelado dele. Retorna só "sim/não", então é rápido. Trocar por NOT EXISTS daria os clientes sem nenhum cancelamento.

`WHERE col IN (SELECT ...)`

SELECT nome, email
FROM clientes
WHERE id IN (
  SELECT cliente_id
  FROM pedidos
  GROUP BY cliente_id
  HAVING SUM(valor_total) > 20000
);

A subconsulta devolve uma lista de cliente_id (os que gastaram mais de 20 mil); o IN mantém os clientes cujo id está nessa lista. Funciona bem quando a subconsulta devolve poucas linhas.

Pegadinhas deste módulo

Os erros que quase todo mundo comete aqui. Ler agora poupa meia hora de confusão depois, e alguns deles são a causa de relatório errado em projeto real.

Zero linhas pode significar "está tudo certo" ou "a query está errada". São duas conclusões opostas para o mesmo resultado. Antes de dizer "não há inconsistência", prove que a query funciona: rode uma versão sem o filtro, ou com o filtro invertido, e veja se ela consegue devolver alguma coisa.
Comparar número decimal com = falha por arredondamento. valor_total = SUM(quantidade * preco_unitario) pode dar diferente por casas decimais invisíveis. Use ROUND(..., 2) dos dois lados, ou compare a diferença absoluta contra uma tolerância.
Achar a linha errada é metade do trabalho. A outra metade é decidir se o dado está torto ou se a regra que você assumiu está errada. Antes de abrir o defeito, confirme qual é a regra: às vezes o defeito está na especificação (Fundamentos, Módulo 5).
Métrica sem denominador engana. "40 defeitos no checkout" não diz nada sem saber quantas entregas houve. Prefira taxas, e diga sempre o período e o recorte.
A média esconde o caso ruim. Tempo médio de correção de 3 dias pode conter um defeito parado há 200. Olhe também o máximo e a mediana antes de apresentar o número.

Prática

Escreva a query e clique Rodar (ou Ctrl+Enter).

carregando banco…

Query livre

Olhar de QA

Funções de texto e data são onde o dado sujo aparece: espaço sobrando no fim do nome, e-mail com maiúscula, data guardada como texto em formato inconsistente. Na prática, boa parte do que parece bug de sistema é dado mal normalizado, e conseguir provar isso com uma consulta, em vez de suspeitar, muda a conversa com o time inteiro.

Módulo 6: JOIN, subconsultas e CTE