Módulo 5: Subconsultas e CTE

subquery, WITH, EXISTS

Teoria

Subconsulta = um SELECT dentro de outro. Pode ir no WHERE ... IN (SELECT ...), no FROM (SELECT ...), ou como valor escalar: ... > (SELECT AVG(...)).
WITH nome AS (SELECT ...) SELECT ... FROM nome: é a CTE, que dá nome a uma subconsulta e deixa a query legível. Encadeie várias com vírgula.
EXISTS (SELECT 1 FROM ... WHERE ...): testa se existe pelo menos uma linha. NOT EXISTS pro contrário.
Subconsulta escalar retorna 1 valor; IN/EXISTS lidam com listas.

Olhar de QA: "clientes que têm algum pedido cancelado", "linhas acima da média": perguntas em duas camadas viram subconsulta ou CTE.

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.

Subconsulta no `WHERE ... IN`

SELECT nome, email
FROM clientes
WHERE id IN (
  SELECT cliente_id
  FROM pedidos
  WHERE status = 'cancelado'
);

A subconsulta interna devolve uma lista de cliente_id com pedido cancelado. O WHERE id IN (...) de fora mantém os clientes cujo id está nessa lista. A mesma ideia caberia no FROM (SELECT ...) (como tabela) ou, se voltasse um número só, comparada direto: WHERE valor_total > (SELECT AVG(valor_total) FROM pedidos).

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

WITH pedidos_validos AS (
  SELECT * FROM pedidos WHERE status <> 'cancelado'
)
SELECT cliente_id, COUNT(*) AS qtd
FROM pedidos_validos
GROUP BY cliente_id;

WITH nome AS (...) dá nome a uma subconsulta e a deixa no topo, legível. Depois você usa pedidos_validos como se fosse uma tabela. Dá pra encadear várias, separadas por vírgula.

`EXISTS` / `NOT EXISTS`

SELECT c.nome
FROM clientes AS c
WHERE EXISTS (
  SELECT 1
  FROM pedidos AS p
  WHERE p.cliente_id = c.id
    AND p.valor_total > 5000
);

EXISTS só responde "tem pelo menos uma linha?". Pra cada cliente, checa se há algum pedido dele acima de 5000; se há, o cliente entra. O SELECT 1 é convenção: o valor não importa, só a existência. NOT EXISTS inverteria.

Escalar (1 valor) vs lista

SELECT nome, preco
FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos);

(SELECT AVG(preco) FROM produtos) devolve um único número (a média geral), então dá pra comparar com > direto. Se a subconsulta devolvesse várias linhas, seria preciso IN ou EXISTS, não >.

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.

NOT IN com NULL na subconsulta devolve zero linhas. Se a subconsulta de WHERE id NOT IN (SELECT cliente_id FROM pedidos) contiver um único NULL, o resultado é vazio, sempre, sem erro. Prefira NOT EXISTS, que não tem esse problema, ou acrescente WHERE cliente_id IS NOT NULL na subconsulta.
Subconsulta escalar precisa devolver exatamente uma linha. = (SELECT ...) que devolve duas linhas dá erro; que devolve nenhuma vira NULL e a comparação falha em silêncio.
CTE não é otimização, é legibilidade. WITH deixa a query compreensível, mas em vários bancos não torna nada mais rápido. Use pela clareza, que já é motivo suficiente.
EXISTS costuma ser melhor que IN em lista grande. EXISTS para assim que acha a primeira linha; IN pode materializar a lista inteira.
Subconsulta correlacionada roda uma vez por linha. Funciona bem em 60 clientes e trava em 6 milhões. Em banco de produção, desconfie e teste com volume antes de rodar.

Prática

Rode com o botão ou Ctrl+Enter.

carregando banco…

Query livre

Terminou este módulo? Marque: o ✓ aparece no índice da trilha e a barra de progresso anda no começo do site. Fica salvo só no seu navegador.