EXISTS e subconsultas correlacionadas

Subconsultas correlacionadas

Até agora a subconsulta era executada uma única vez, de forma independente. Uma subconsulta correlacionada é diferente: referencia uma coluna da consulta externa, então é avaliada uma vez para cada linha de fora. É como um laço: para cada categoria, executa a interna usando aquela categoria.

-- Categorias cujo produto mais caro supera os 100
SELECT nome
FROM categorias c
WHERE 100 < (
  SELECT MAX(p.preco)
  FROM produtos p
  WHERE p.categoria_id = c.id   -- <- referência à linha externa "c"
)
ORDER BY nome;

A chave é o alias: c é definido fora e usado dentro. É isso que "correlaciona" ambas as consultas.

EXISTS

EXISTS recebe uma subconsulta e retorna verdadeiro se essa subconsulta produzir pelo menos uma linha (não importa qual linha: apenas se existe ou não). É usado quase sempre correlacionado, para perguntar "há alguma linha relacionada?":

-- Categorias que TÊM ao menos um produto
SELECT nome
FROM categorias c
WHERE EXISTS (
  SELECT 1 FROM produtos p WHERE p.categoria_id = c.id
)
ORDER BY nome;

Por convenção, escreve-se SELECT 1 dentro de EXISTS: como só importa a existência, as colunas específicas são irrelevantes.

Sua negação, NOT EXISTS, encontra o contrário: categorias sem produtos.

SELECT nome
FROM categorias c
WHERE NOT EXISTS (
  SELECT 1 FROM produtos p WHERE p.categoria_id = c.id
)
ORDER BY nome;

EXISTS vs IN: muitas vezes dão o mesmo resultado, mas EXISTS costuma ser mais claro e robusto quando há correlação ou possíveis NULL, e pode parar assim que encontra a primeira correspondência.

Exemplos

EXISTS: categorias com estoque disponível

SELECT nome
FROM categorias c
WHERE EXISTS (
  SELECT 1 FROM produtos p
  WHERE p.categoria_id = c.id AND p.estoque > 0
)
ORDER BY nome;

NOT EXISTS: categorias sem nenhum produto em estoque

SELECT nome
FROM categorias c
WHERE NOT EXISTS (
  SELECT 1 FROM produtos p
  WHERE p.categoria_id = c.id AND p.estoque > 0
)
ORDER BY nome;