Subconsultas, IN e EXISTS

Subconsulta é um SELECT dentro de outro. Ela pode devolver um valor (escalar), uma lista (para IN) ou só "existe ou não" (para EXISTS).

Escalar: um valor só

SELECT id, valor,
       ROUND(valor - (SELECT AVG(valor) FROM pedidos), 1) AS acima_da_media
FROM pedidos
WHERE valor > (SELECT AVG(valor) FROM pedidos)
ORDER BY valor DESC, id;

id  | valor | acima_da_media
--- | ----- | --------------
105 | 1050  | 650
102 | 900   | 500
110 | 900   | 500

Se a subconsulta escalar devolver mais de uma linha, o PostgreSQL e o Spark dão erro; o SQLite pega a primeira, em silêncio. Mais um motivo para não depender do SQLite como referência de padrão.

EXISTS correlacionada

Ela é "correlacionada" porque usa a linha de fora (c.id). Lê-se: clientes para os quais existe algum pedido acima de 500.

SELECT c.nome
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id AND p.valor > 500)
ORDER BY c.nome;

nome
-----
Ana
Bruno
Diego

IN com lista calculada

SELECT nome
FROM produtos
WHERE id IN (SELECT produto_id FROM itens)
ORDER BY nome;

nome
---------
Cabo HDMI
Monitor
Mouse
Teclado
Webcam

• IN (subconsulta) e EXISTS dão o mesmo resultado; NOT IN e NOT EXISTS, não, se houver NULL (veja NULL e a lógica de três valores).
• O otimizador costuma transformar EXISTS e IN num semi-join. No Spark, é o mesmo plano de um LEFT SEMI JOIN.