Subconsultas Correlacionadas em SQL

Lição 6.3 · Tempo de leitura: ~6 min · Tarefas para praticar ↓

Uma subconsulta correlacionada em SQL é uma subconsulta que faz referência a uma coluna da consulta externa, por isso o seu resultado depende da linha externa atual. Nesta lição, você vai aprender como uma subconsulta correlacionada é avaliada, como comparar uma linha com o seu próprio grupo, como usar subconsultas correlacionadas no SELECT e com EXISTS, e quando um JOIN é a melhor escolha.

As subconsultas das lições anteriores eram independentes: era possível selecionar a consulta interna e executá-la sozinha. Uma subconsulta correlacionada não pode ser executada sozinha, porque usa um valor da linha que a consulta externa está a examinar naquele momento. Isso permite fazer perguntas relativas a cada linha: "este filme é mais caro do que a média da sua classificação?", "qual é o filme mais longo desta categoria?".

Como Funciona uma Subconsulta Correlacionada

Logicamente, o banco de dados avalia uma subconsulta correlacionada assim:

1. Pega uma linha da consulta externa.
2. Executa a consulta interna, substituindo os valores dessa linha.
3. Usa o resultado para avaliar a condição ou a coluna da linha.
4. Passa para a próxima linha e repete.

A ligação entre as consultas é uma condição na consulta interna que usa um alias externo, por exemplo f2.rating = f1.rating. Os alias são essenciais: sem eles, não fica claro a qual cópia da tabela pertence uma coluna.

Comparar uma Linha com o Seu Grupo

Filmes cujo custo de reposição é maior do que a média da sua classificação:

SELECT f1.title, f1.rating, f1.replacement_cost
FROM film AS f1
WHERE f1.replacement_cost > (
    SELECT AVG(f2.replacement_cost)
    FROM film AS f2
    WHERE f2.rating = f1.rating
)
ORDER BY f1.rating, f1.replacement_cost DESC;

Resultado: os filmes cuja reposição custa mais do que a do filme médio da mesma classificação.

Para um filme com classificação PG, a consulta interna calcula a média dos filmes PG; para um filme R, a média dos filmes R. Uma subconsulta não correlacionada só conseguiria comparar com uma única média geral.

Subconsultas Correlacionadas no SELECT

Uma subconsulta correlacionada na lista do SELECT devolve um valor para cada linha. O número de filmes de cada categoria:

SELECT c.name AS category,
       (SELECT COUNT(*)
        FROM film_category AS fc
        WHERE fc.category_id = c.category_id) AS films
FROM category AS c
ORDER BY films DESC;

Resultado: 16 categorias com o número de filmes, a começar por Sports (74 filmes).

Ela também pode devolver um valor difícil de obter com GROUP BY, como o título do filme mais longo de cada categoria:

SELECT c.name AS category,
       (SELECT f.title
        FROM film AS f
        INNER JOIN film_category AS fc ON fc.film_id = f.film_id
        WHERE fc.category_id = c.category_id
        ORDER BY f.length DESC, f.title
        LIMIT 1) AS longest_film
FROM category AS c
ORDER BY c.name;

Resultado: cada categoria com o título do seu filme mais longo; empates são resolvidos pelo título.

Uma subconsulta no SELECT deve devolver uma linha e uma coluna; caso contrário, a consulta falha.

Subconsultas Correlacionadas com EXISTS

O EXISTS verifica se a consulta interna devolve pelo menos uma linha, e quase sempre é correlacionado. Clientes que alugaram pelo menos uma cópia da loja 2:

SELECT c.customer_id, c.first_name, c.last_name
FROM customer AS c
WHERE EXISTS (
    SELECT 1
    FROM rental AS r
    INNER JOIN inventory AS i ON i.inventory_id = r.inventory_id
    WHERE r.customer_id = c.customer_id
      AND i.store_id = 2
)
ORDER BY c.customer_id;

Resultado: os clientes com pelo menos um aluguel de uma cópia da loja 2. SELECT 1 é uma convenção: o EXISTS só verifica se uma linha existe, não o que ela contém.

O NOT EXISTS encontra linhas sem correspondência, como o anti-join LEFT JOIN ... IS NULL da Lição 5.3.

Desempenho

A descrição "executada para cada linha" é o modelo lógico. Os otimizadores modernos muitas vezes reescrevem subconsultas correlacionadas como junções ou semijunções, especialmente EXISTS e IN. Mesmo assim:

• Uma subconsulta correlacionada no SELECT ou num WHERE complexo pode realmente ser executada uma vez por linha externa e ficar lenta em tabelas grandes.
• Um índice na coluna de correlação (aqui film_category.category_id, rental.customer_id) torna cada execução interna rápida.
• Se o mesmo resultado interno é necessário para muitas colunas, um JOIN com uma subconsulta agrupada ou uma CTE (Lição 6.4) costuma ser mais claro e mais rápido.

Se uma consulta com subconsulta correlacionada estiver lenta, verifique o plano com EXPLAIN.

Principais conclusões:

• Uma subconsulta correlacionada faz referência a uma coluna da consulta externa e não pode ser executada sozinha.
• Logicamente, é avaliada uma vez para cada linha externa.
• É a ferramenta natural para comparar uma linha com o seu próprio grupo.
• No SELECT, deve devolver exatamente um valor.
• EXISTS e NOT EXISTS são subconsultas correlacionadas típicas.
• Indexe a coluna de correlação; considere um JOIN ou uma CTE em consultas pesadas.

Perguntas Frequentes

Qual é a diferença entre uma subconsulta correlacionada e uma não correlacionada?

Uma subconsulta não correlacionada é independente: é executada uma vez e o seu resultado serve para todas as linhas. Uma subconsulta correlacionada usa valores da linha externa, por isso o seu resultado pode ser diferente para cada linha.

Subconsultas correlacionadas são lentas?

Podem ser, porque logicamente são executadas para cada linha externa. Na prática, os otimizadores muitas vezes as convertem em junções, e um índice na coluna de correlação ajuda muito. Verifique o plano de execução com EXPLAIN.

Uma subconsulta correlacionada pode ser substituída por um JOIN?

Normalmente, sim. Por exemplo, a comparação com a média do grupo pode ser escrita como uma junção com uma subconsulta agrupada: JOIN (SELECT rating, AVG(replacement_cost) AS avg_cost FROM film GROUP BY rating) AS r ON r.rating = f.rating.