Subconsultas escalares e em WHERE

O que é uma subconsulta?

Uma subconsulta (ou consulta aninhada) é uma consulta SELECT escrita dentro de outra consulta, entre parênteses. A interna é executada primeiro e seu resultado alimenta a externa. É a forma natural de responder perguntas que dependem de um dado que precisa ser calculado antes.

Subconsulta escalar

Uma subconsulta escalar retorna um único valor (uma linha e uma coluna). Por isso você pode usá-la em qualquer lugar onde caiba um valor, como o lado direito de uma comparação:

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

Primeiro AVG(preco) é calculado (um número), e depois as linhas são comparadas contra ele. Se a subconsulta retornasse mais de uma linha aqui, o SQLite daria erro: uma comparação com > espera um único valor.

Truque mental: leia a subconsulta como se fosse uma constante que você ainda não conhece. "Produtos mais caros que a média".

Subconsultas com IN

Quando a subconsulta retorna uma coluna com vários valores, você não pode usar =, mas pode usar IN, que verifica a pertinência a um conjunto:

SELECT nome
FROM produtos
WHERE categoria_id IN (
  SELECT id FROM categorias WHERE nome IN ('perifericos', 'monitores')
)
ORDER BY nome;

A subconsulta produz a lista de id dessas categorias; a externa fica com os produtos cujo categoria_id está nessa lista. Seu oposto é NOT IN.

Cuidado com NULL: se a subconsulta de um NOT IN puder retornar NULL, o resultado se torna imprevisível. Nesses casos costuma-se preferir NOT EXISTS (próxima lição).

Exemplos

Produtos acima do preço médio

-- Substitua mentalmente a subconsulta pelo seu valor (a média):
SELECT nome, preco
FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos)
ORDER BY preco DESC;

IN com uma lista calculada por outra consulta

SELECT nome
FROM produtos
WHERE categoria_id IN (SELECT id FROM categorias WHERE nome = 'computadores')
ORDER BY nome;