SQL Subconsultas

A Ana quer saber quais produtos da Loja Aurora custam mais que a média. São duas perguntas, uma depois da outra: primeiro "qual é a média?", depois "quem está acima dela?". Em SQL, dá para fazer as duas de uma vez, pondo uma consulta dentro da outra. A consulta de dentro se chama subconsulta (em inglês, subquery): ela roda primeiro, e o resultado dela vira parte da consulta de fora.

Você vai aprender

A subconsulta escalar: um valor só, usado no WHERE e no SELECT.
A subconsulta que devolve uma lista, com IN e NOT IN.
A subconsulta no FROM, que funciona como uma tabela temporária.
A subconsulta correlacionada, que roda uma vez para cada linha de fora.
Quando a mesma pergunta fica melhor com JOIN.
A armadilha do NOT IN com NULL e os erros reais do SQLite.

Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora (veja O Banco da Loja Aurora). Esta aula usa AVG(), MAX(), IN e GROUP BY.

A ideia: perguntar a quem sabe

A Ana está no caixa e precisa saber se um produto está acima do preço médio da loja. Ela não sabe a média de cabeça, então pergunta à colega do escritório: "qual é o preço médio?". A colega faz a conta e responde. Com a resposta, a Ana decide.

A subconsulta é a colega do escritório. A consulta de fora (a Ana) faz uma pergunta no meio do caminho, a de dentro responde, e a de fora usa a resposta:

O que a subconsulta devolve	Onde costuma ir	Exemplo	Seção
Um valor só (1 linha, 1 coluna)	`WHERE preco > (…)`, no `SELECT`	o preço médio	1 e 2
Uma lista (várias linhas, 1 coluna)	`WHERE id IN (…)`	os clientes que pediram	3
Uma tabela (várias linhas e colunas)	`FROM (…)`	o total de cada pedido	4

Em todos os casos, a subconsulta vai entre parênteses. É assim que o banco sabe onde ela começa e termina.

1. A subconsulta escalar no WHERE

Primeiro, a pergunta da colega, sozinha:

SELECT AVG(preco) FROM produtos;

A média é 48.2053333333333. A Ana poderia copiar esse número para dentro de outra consulta (WHERE preco > 48.2), mas aí, no dia em que um preço mudar, o número copiado fica velho. Melhor deixar o banco calcular na hora, pondo a pergunta inteira no lugar do número:

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

(SELECT AVG(preco) FROM produtos): A subconsulta. Ela roda primeiro e devolve um valor só: a média. Uma subconsulta que devolve um valor só (uma linha, uma coluna) se chama escalar.
WHERE preco > ( … ): A consulta de fora usa esse valor como se você tivesse digitado o número. O resultado: os 14 produtos acima da média.
Os parênteses: Obrigatórios. Sem eles, o banco não sabe que o segundo SELECT é uma pergunta dentro da primeira.

O mesmo jeito serve para achar "o mais" de alguma coisa. O produto mais caro da loja:

SELECT nome, preco
FROM produtos
WHERE preco = (SELECT MAX(preco) FROM produtos);

O Jogo de Panelas, a 249.9. E se dois produtos empatassem no preço máximo, os dois apareceriam, o que é uma vantagem sobre um ORDER BY preco DESC LIMIT 1, que mostraria só um.

Missão: mostre só os funcionários que ganham mais que a média dos salários da loja. Use uma subconsulta com AVG(salario), sem digitar o valor da média.

SELECT nome, salario
FROM funcionarios;

SELECT nome, salario
FROM funcionarios
WHERE salario > (SELECT AVG(salario) FROM funcionarios);

2. A subconsulta escalar no SELECT

Um valor só pode ir também na lista de colunas. Aqui, para cada produto da Papelaria, quanto ele está acima ou abaixo da média da loja:

SELECT nome,
       preco,
       ROUND(preco - (SELECT AVG(preco) FROM produtos), 2) AS diferenca
FROM produtos
WHERE categoria_id = 1;

A subconsulta devolve a média; a conta preco - média é feita em cada linha, e o ROUND(…, 2) arredonda para 2 casas (aula Funções Numéricas). Número negativo é "abaixo da média": o lápis está 47.06 abaixo; a mochila, 41.78 acima.

3. A lista: subconsulta com IN

A subconsulta também pode devolver várias linhas de uma coluna, uma lista. Aí ela vai com o IN (aula IN), no lugar daquela lista que você escreveria à mão, IN (7, 8, 9).

A Ana quer os clientes que fizeram pedido de setembro de 2026 para cá. A tabela pedidos sabe quem pediu, mas só pelo número (cliente_id). Primeiro, a lista de números:

SELECT cliente_id FROM pedidos WHERE data >= '2026-09-01';

Seis números, de 7 a 12. Agora ponha essa consulta dentro do IN, e o banco busca os nomes:

SELECT nome, cidade
FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos WHERE data >= '2026-09-01');

WHERE id IN ( … ): "O id do cliente está na lista que a subconsulta devolver."
SELECT cliente_id FROM pedidos …: A subconsulta devolve uma coluna só: a lista de números. Com IN, ela pode ter quantas linhas quiser (até nenhuma).

NOT IN: quem não está na lista

Com NOT IN, a pergunta vira o contrário. Os clientes que nunca fizeram pedido:

SELECT id, nome
FROM clientes
WHERE id NOT IN (SELECT cliente_id FROM pedidos);

Os clientes 27 a 30 se cadastraram e nunca compraram. Do mesmo jeito, os produtos que nunca foram vendidos (que não aparecem em nenhum item de pedido):

SELECT id, nome
FROM produtos
WHERE id NOT IN (SELECT produto_id FROM itens_pedido);

Os três últimos produtos de jardinagem. Cuidado com o NOT IN quando a lista pode ter NULL: ele simplesmente para de devolver linhas. É uma armadilha famosa, que está em Quando dá errado.

Missão: quais categorias têm algum produto descontinuado? Filtre a consulta abaixo com id IN (…) e uma subconsulta que liste o categoria_id dos produtos com descontinuado = 1.

SELECT nome
FROM categorias;

SELECT nome
FROM categorias
WHERE id IN (SELECT categoria_id FROM produtos WHERE descontinuado = 1);

4. A subconsulta no FROM: uma tabela de passagem

A Ana quer o ticket médio da loja: quanto vale, em média, um pedido. Cada pedido tem vários itens, então são dois passos: somar os itens de cada pedido e, depois, tirar a média desses totais. O primeiro passo, sozinho (só os 5 primeiros, para ver o formato):

SELECT pedido_id, SUM(quantidade * preco_unitario) AS total
FROM itens_pedido
GROUP BY pedido_id
LIMIT 5;

O resultado tem cara de tabela: uma linha por pedido, com o total. Uma subconsulta no FROM pode fazer o papel de uma tabela:

SELECT ROUND(AVG(total), 2) AS ticket_medio
FROM (
  SELECT pedido_id, SUM(quantidade * preco_unitario) AS total
  FROM itens_pedido
  GROUP BY pedido_id
) AS t;

FROM ( … ): No lugar do nome de uma tabela, uma consulta inteira. O banco roda a de dentro e a de fora lê o resultado como se fosse uma tabela. Ela também é chamada de tabela derivada.
AS total: O apelido da coluna de dentro. É por esse nome que a consulta de fora a enxerga: AVG(total).
AS t: O apelido da tabela derivada. O SQLite aceita sem ele, mas outros bancos exigem (veja a nota "Nos outros bancos"). Ponha sempre.

O ticket médio é 205.97 (sem contar o frete). Repare por que não dava para fazer numa consulta só: AVG(quantidade * preco_unitario) direto na tabela daria 84.26, a média de cada item, e não de cada pedido.

Você já usou esse jeito na aula UNION ALL, para contar o resultado de um UNION ALL. Quando a subconsulta do FROM fica grande, ou é usada mais de uma vez, existe uma escrita mais limpa para ela: a CTE (WITH), no Nível 3.

5. A subconsulta correlacionada

Até aqui, a subconsulta rodava uma vez, e a resposta valia para todas as linhas. Agora uma pergunta diferente: quais produtos custam mais que a média da própria categoria? A média muda de categoria para categoria, então a subconsulta precisa saber de qual produto se está falando:

SELECT p.nome, p.categoria_id, p.preco
FROM produtos AS p
WHERE p.preco > (SELECT AVG(p2.preco)
                 FROM produtos AS p2
                 WHERE p2.categoria_id = p.categoria_id)
ORDER BY p.categoria_id;

FROM produtos AS p: A consulta de fora, com o apelido p.
FROM produtos AS p2: A de dentro lê a mesma tabela, com outro apelido (p2), como no Self Join.
WHERE p2.categoria_id = p.categoria_id: O pulo do gato: a subconsulta usa uma coluna da consulta de fora (p.categoria_id). Por isso ela é correlacionada: o resultado dela depende da linha que está sendo examinada lá fora.

Para entender, siga a Mochila Escolar Azul:

Lá fora, o banco está olhando a mochila: p.categoria_id = 1, p.preco = 89.99.
A subconsulta roda com p2.categoria_id = 1 e calcula a média da Papelaria: 21.78.
89.99 > 21.78? Sim: a mochila entra no resultado.
Na próxima linha (o caderno, também da Papelaria), a subconsulta roda de novo. Num produto de Eletrônicos, ela calcula a média de Eletrônicos, e assim por diante.

São 18 produtos. Uma subconsulta correlacionada também pode ir no SELECT. Aqui, o número de pedidos de cada cliente (os de Pernambuco e os que nunca compraram):

SELECT c.nome,
       (SELECT COUNT(*) FROM pedidos AS pe WHERE pe.cliente_id = c.id) AS pedidos
FROM clientes AS c
WHERE c.estado = 'PE' OR c.id > 26;

Para cada cliente, a subconsulta conta os pedidos daquele c.id. Quem nunca pediu ganha 0, e não some do resultado.

Correlacionada roda muitas vezes

Na ideia, a subconsulta correlacionada roda uma vez por linha de fora: com 45 produtos, 45 vezes. O banco tem truques para acelerar (como os índices), mas, em tabelas enormes, vale comparar com a versão em JOIN + GROUP BY.

6. Formas diferentes: subconsulta × JOIN

Muitas perguntas com IN também se respondem com um JOIN. Os clientes que pediram de setembro de 2026 para cá, da seção 3, com INNER JOIN:

SELECT DISTINCT c.nome, c.cidade
FROM clientes AS c
INNER JOIN pedidos AS pe ON pe.cliente_id = c.id
WHERE pe.data >= '2026-09-01';

Mesmos 6 clientes. Repare no DISTINCT: o JOIN repete o cliente uma vez para cada pedido dele, e o IN não, porque só pergunta "está na lista?". E os clientes que nunca pediram, com LEFT JOIN:

SELECT c.nome
FROM clientes AS c
LEFT JOIN pedidos AS pe ON pe.cliente_id = c.id
WHERE pe.id IS NULL;

Pergunta	Com subconsulta	Com JOIN	Outra forma
Quem tem pedido?	`WHERE id IN (SELECT cliente_id …)`	`INNER JOIN` + `DISTINCT`	`EXISTS`
Quem não tem pedido?	`WHERE id NOT IN (…)` (cuidado com NULL)	`LEFT JOIN … WHERE … IS NULL`	`NOT EXISTS`
Acima da média geral	`WHERE preco > (SELECT AVG …)`	difícil	—
Preciso de colunas das duas tabelas	só uma por subconsulta no `SELECT`	`JOIN`: é para isso que ele existe	—

Regra prática: se você só quer filtrar por outra tabela, a subconsulta costuma ser mais fácil de ler. Se quer mostrar colunas da outra tabela, use JOIN.

Quando dá errado

Função agregada direto no WHERE

A primeira tentativa de quase todo mundo:

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

A mensagem: misuse of aggregate function AVG(), "uso indevido da função agregada AVG()". O WHERE examina uma linha por vez, e uma linha sozinha não tem média.

Como consertar: ponha a média numa subconsulta: WHERE preco > (SELECT AVG(preco) FROM produtos).

A subconsulta "escalar" devolveu várias linhas

A Ana queria os produtos com o mesmo preço de algum item da Papelaria, e usou =:

SELECT nome, preco
FROM produtos
WHERE preco = (SELECT preco FROM produtos WHERE categoria_id = 1);

O que acontece: nenhum erro, e só o caderno no resultado. A Papelaria tem 6 preços, mas, quando uma subconsulta usada como valor devolve várias linhas, o SQLite pega a primeira e ignora o resto, em silêncio. Os outros bancos dão erro nesse caso (veja a nota abaixo).

Como consertar: se a subconsulta pode devolver uma lista, use IN: WHERE preco IN (SELECT preco FROM produtos WHERE categoria_id = 1). Se você queria mesmo um valor só, garanta isso com uma função agregada (MAX, AVG…).

Colunas demais na subconsulta

SELECT nome
FROM clientes
WHERE id IN (SELECT cliente_id, data FROM pedidos);

A mensagem: sub-select returns 2 columns - expected 1, "a subconsulta devolve 2 colunas, era esperada 1". O IN compara o id com uma coluna; o banco não sabe o que fazer com a data. A mesma mensagem aparece com uma subconsulta de 2 colunas no SELECT.

Como consertar: deixe só a coluna que vai ser comparada: SELECT cliente_id FROM pedidos.

Esqueceu os parênteses

SELECT nome
FROM clientes
WHERE id IN SELECT cliente_id FROM pedidos;

A mensagem: near "SELECT": syntax error, "erro de sintaxe perto de SELECT". Sem parênteses, o banco não reconhece o segundo SELECT como subconsulta.

Como consertar: toda subconsulta vai entre parênteses: IN (SELECT cliente_id FROM pedidos).

O NOT IN que não devolve nada

A Ana quer os clientes de estados onde a loja não tem fornecedor. Os fornecedores estão no PI, CE e SP, mais dois estrangeiros, com estado NULL. Então deveriam sair os clientes de Pernambuco:

SELECT nome, estado
FROM clientes
WHERE estado NOT IN (SELECT estado FROM fornecedores);

O que acontece: zero linhas, sem erro. A lista da subconsulta tem um NULL. Para o banco, "PE não está em (PI, CE, SP, NULL)" não é verdadeiro: como NULL é "desconhecido", ele não pode garantir que PE é diferente daquele valor desconhecido (aula O Valor NULL). A resposta vira "desconhecido", e o WHERE descarta a linha. Isso acontece com todos os clientes.

Como consertar: tire os NULL da lista:

SELECT nome, estado
FROM clientes
WHERE estado NOT IN (SELECT estado FROM fornecedores WHERE estado IS NOT NULL);

Agora saem a Juliana e o Bruno, de Pernambuco. Outra saída é o NOT EXISTS, que não tem essa armadilha (aula EXISTS).

A subconsulta escalar não achou nada

SELECT nome
FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos WHERE categoria_id = 99);

O que acontece: zero linhas, sem erro. Não existe categoria 99, então a média é NULL, e nenhuma comparação com NULL é verdadeira. Se uma subconsulta escalar não encontra nenhuma linha, o resultado dela é NULL.

Como consertar: confira o filtro de dentro rodando a subconsulta sozinha; ela tem de devolver o valor que você espera.

Nos outros bancos

Subconsulta escalar com várias linhas: o SQLite pega a primeira em silêncio; os outros bancos param com erro. No MySQL é o erro 1242, Subquery returns more than 1 row; o manual do PostgreSQL diz que usar como escalar uma consulta que devolve mais de uma linha "é um erro". Nos dois, uma subconsulta escalar sem nenhuma linha vale NULL, como no SQLite.
Apelido da tabela derivada: no MySQL, o apelido depois de FROM (…) é obrigatório ("toda tabela no FROM precisa ter um nome"). Escreva sempre FROM (…) AS t, que funciona em todos.

Referência rápida

Escrita	O que faz
`WHERE preco > (SELECT AVG(preco) FROM produtos)`	Escalar: compara com um valor calculado na hora
`SELECT nome, (SELECT …) AS x FROM …`	Escalar no `SELECT`: uma coluna calculada por outra consulta
`WHERE id IN (SELECT cliente_id FROM pedidos)`	Lista: está entre os valores devolvidos
`WHERE id NOT IN (SELECT … WHERE … IS NOT NULL)`	Lista: não está entre os valores (tire os `NULL`)
`FROM (SELECT …) AS t`	Tabela derivada: uma consulta no lugar de uma tabela
`WHERE p.preco > (SELECT AVG(p2.preco) … WHERE p2.categoria_id = p.categoria_id)`	Correlacionada: a de dentro usa uma coluna da de fora

Mensagem do SQLite	Quer dizer
`misuse of aggregate function AVG()`	Agregada direto no `WHERE`: use uma subconsulta
`sub-select returns 2 columns - expected 1`	A subconsulta tem colunas demais para `IN` ou para um valor
`near "SELECT": syntax error`	Faltaram os parênteses em volta da subconsulta
(nenhuma, 0 linhas)	`NOT IN` com `NULL` na lista, ou subconsulta escalar sem resultado

Exercícios

1. O preço mais barato

Complete para mostrar o produto mais barato da loja:

SELECT nome FROM produtos WHERE preco = (SELECT FROM produtos);

2. Na lista

Complete para mostrar os produtos que já foram vendidos alguma vez:

SELECT nome FROM produtos WHERE id (SELECT produto_id FROM itens_pedido);

3. O que é uma subconsulta correlacionada?

Uma subconsulta que fica no FROM Uma subconsulta que devolve várias colunas Uma subconsulta que usa uma coluna da consulta de fora, e por isso roda de novo para cada linha Uma subconsulta com JOIN

4. A Ana escreveu WHERE estado NOT IN (SELECT estado FROM fornecedores) e veio 0 linhas. A causa mais provável:

A subconsulta devolve algum NULL, e aí o NOT IN nunca é verdadeiro Faltou o DISTINCT na subconsulta NOT IN não funciona com subconsulta A subconsulta tem colunas demais

5. No SQLite, o que acontece com WHERE preco = (SELECT preco FROM produtos WHERE categoria_id = 1), se a subconsulta devolve 6 linhas?

Dá erro: subconsulta com mais de uma linha O SQLite usa só a primeira linha, sem avisar Ele compara com as 6, como um IN O resultado vem vazio

6. Por que AVG(quantidade * preco_unitario) direto em itens_pedido não dá o ticket médio por pedido?

Porque AVG não aceita contas dentro Porque falta o ORDER BY Porque o AVG ignora os decimais Porque ele tira a média de cada item, e não de cada pedido; é preciso somar por pedido antes, numa subconsulta no FROM

Missão final: quem comprou o mais caro

Missão: a consulta abaixo já acha os pedidos que têm o produto mais caro da loja (sem digitar o número dele). A Ana quer o nome e a cidade dos clientes que fizeram esses pedidos. Use a consulta abaixo como subconsulta: os pedidos ficam na tabela pedidos (coluna cliente_id), e os nomes, em clientes. (Dica: são dois IN, um dentro do outro.)

SELECT pedido_id
FROM itens_pedido
WHERE produto_id = (SELECT id FROM produtos
                    WHERE preco = (SELECT MAX(preco) FROM produtos));

SELECT nome, cidade
FROM clientes
WHERE id IN (SELECT cliente_id
             FROM pedidos
             WHERE id IN (SELECT pedido_id
                          FROM itens_pedido
                          WHERE produto_id = (SELECT id FROM produtos
                                              WHERE preco = (SELECT MAX(preco) FROM produtos))));

Leia a solução de dentro para fora: o preço máximo → o produto com esse preço → os pedidos que têm esse produto → os clientes desses pedidos. Cada parêntese responde uma pergunta para o de fora.

Resumo

Uma subconsulta é uma consulta entre parênteses dentro de outra. Ela responde uma pergunta no meio do caminho.
Escalar (um valor só): vai no WHERE (preco > (SELECT AVG(preco) …)) ou no SELECT. Se não acha nada, vale NULL; se acha várias linhas, o SQLite usa a primeira em silêncio.
Lista (uma coluna): vai com IN e NOT IN. No NOT IN, tire os NULL da lista.
No FROM: a subconsulta vira uma tabela de passagem; dê um apelido (AS t).
Correlacionada: a de dentro usa uma coluna da de fora e roda de novo para cada linha.
Para só filtrar, a subconsulta é ótima; para mostrar colunas de outra tabela, use JOIN. A próxima aula, EXISTS, mostra mais um jeito de perguntar "tem ou não tem?".