Subconsulta Correlata em SQL: Um guia para iniciantes

Às vezes, o uso de uma subconsulta correlata SQL é a única maneira de resolver uma afirmação. Mas estas subconsultas podem ser muito lentas. Neste post, vamos falar sobre quando usar uma subconsulta correlata, por quê e como fazê-lo.

As subconsultas são um recurso importante para aumentar o poder expressivo do SQL. Se você ainda não leu nosso artigo anterior, as subconsultas são simplesmente uma declaração SELECT dentro de outro SELECT. Podemos utilizá-las em diferentes lugares dentro de um SELECT, como nas cláusulas WHERE, HAVING, ou FROM. Podemos até usá-las em outras declarações como UPDATE ou DELETE.

Como subconsultas simples, uma subconsulta SQL correlacionada contém uma consulta dentro de uma consulta. Ela recebe seu nome porque as duas consultas estão relacionadas; a consulta interna usa informações obtidas da consulta externa (por exemplo, de uma tabela referenciada na consulta externa). Para os programadores entre vocês, isto é semelhante a uma estrutura de loop aninhado.

Antes de prosseguirmos para um exemplo de uma subconsulta correlata em SQL, vamos fazer uma rápida atualização em subconsultas simples (não correlatas).

Uma Subconsulta Simples

O seguinte exemplo SQL usa uma simples subconsulta para obter uma lista de funcionários que ganham mais do que o salário médio da empresa. Neste caso, a subconsulta é mostrada em negrito, e seu resultado é o salário médio da empresa. A consulta externa compara o salário de cada funcionário com a média da empresa. Fácil!

SELECT 
  lastname, 
  firstname, 
  salary 
FROM employee 
WHERE salary > (SELECT avg(salary) 
                FROM employee)

Exemplo 1: Uma simples subconsulta SQL não-correlacionada

A seguir estão as subconsultas correlatas. Estas são muito especiais, porque às vezes são a única maneira de resolver uma consulta. Entretanto, pense duas vezes antes de usar uma subconsulta correlata em SQL. Elas podem ser lentas, como explicaremos mais adiante.

Usando uma subconsulta correlata em SQL

Vamos começar com um exemplo de uma subconsulta correlata em SQL. Suponhamos que queremos encontrar todos os funcionários com um salário superior ao salário médio de seu departamento. Usaríamos a seguinte consulta. Mais uma vez, eu ousei a subconsulta:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE e1.salary > (SELECT avg(salary)
                   FROM employee e2 
                   WHERE e2.dept_id = e1.dept_id)

Exemplo 2: Uma subconsulta correlata em SQL

A principal diferença entre uma subconsulta SQL correlacionada e uma subconsulta simples é que as colunas de referência de subconsulta correlacionadas da tabela externa. No exemplo acima, e1.dept_id i é uma referência à tabela de subconsultas externas. Para identificar uma consulta correlata, basta procurar por este tipo de referências. Se você encontrar pelo menos uma, você tem uma subconsulta correlacionada a SQL!

Vejamos outro exemplo. Suponhamos que queremos obter os nomes dos departamentos que têm mais de 10 funcionários. Podemos usar a seguinte subconsulta correlacionada a SQL:

SELECT deptname
FROM department d1
WHERE 10 < (SELECT count(*)
            FROM employee e
            WHERE e.dept_id = d1.dept_id)

Exemplo 3: Outra subconsulta correlacionada em SQL

Tempo para um último exemplo. Mencionamos que as subconsultas podem fazer parte das cláusulas WHERE, FROM, HAVING e SELECT. Neste exemplo, usaremos uma subconsulta correlata SQL na lista SELECT para encontrar o nome de cada funcionário, seu salário e o salário médio de seu departamento. Obteremos o salário médio usando uma subconsulta correlata dentro do SELECT.

Aqui está o código:

SELECT 	
  lastname,
  firstname, 
  salary,
  (SELECT avg(salary) 
    FROM employee e2
    WHERE e2.dep_id = e1.dep_id) AS avg_dept_salary
FROM employee e1

Exemplo 4: Uma subconsulta correlata SQL na lista SELECT

Quantas vezes é executada uma Subconsulta relacionada a SQL Cor?

Suponha que tenhamos uma tabela chamada "assigned_to_project"que armazena os nomes dos funcionários designados aos projetos. Queremos encontrar todos os funcionários que não são designados a nenhum projeto. A solução é a seguinte pergunta:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE NOT EXISTS (SELECT project_id
                  FROM assigned_to_project 
                  WHERE employee_id = e1.employee_id)

Exemplo 5: Uma subconsulta correlata em SQL

Antes de mais nada, esta consulta é fácil de analisar. A cláusula NOT EXISTS é TRUE quando a subconsulta retorna um conjunto de resultados vazio. Isto acontece apenas para aqueles funcionários não designados a nenhum projeto. Novamente, muito fácil!

Entretanto, o objetivo desta seção é analisar quantas vezes a subconsulta correlata SQL é executada. Tente descobrir qual das seguintes afirmações é a correta:

1. É executada apenas uma vez.
2. É executado uma única vez para cada projeto.
3. É executada uma vez para cada funcionário não atribuído a nenhum projeto.
4. É executado uma vez para cada funcionário da empresa.

Vamos analisar estas opções. Suponhamos que tenhamos 1.000 funcionários e 20 projetos. Além disso, temos 800 funcionários que já estão designados a um projeto. Neste caso, a primeira opção tem uma execução, a segunda tem 20 execuções, a terceira tem 200, e a última opção tem 1.000.

Se você disse que a resposta correta foi "uma vez para cada funcionário da empresa", você está correto. Se passarmos pela declaração, esta resposta se torna óbvia; precisamos verificar se cada funcionário tem ou não projetos. De acordo com o exemplo acima, porém, isto significa que a subconsulta será executada 1.000 vezes! Em termos de desempenho, este é o pior cenário dos quatro.

Como as subquisições correlatas tendem a implicar muitas execuções, elas também tendem a ser lentas. Como regra, por causa disto, sempre tentamos evitar o uso de uma subconsulta correlata em SQL. Mas, como já mencionamos, às vezes a correlação é a única maneira de resolver uma consulta.

Quando usar uma subconsulta correlata em SQL

Neste ponto do artigo, o leitor deveria estar pensando: Então, quando usar uma subconsulta correlata em SQL? Na verdade, há alguns casos em que é preciso. Isto é particularmente verdadeiro nas consultas em que estamos procurando o que poderíamos chamar de negativos.

Aqui está um exemplo de uma consulta 'negativa'. Vamos assumir que temos uma tabela de histórico de pagamento com uma coluna chamada payment_type que indica se um pagamento é um salário regular, um bônus ou um prêmio. Se quisermos uma consulta para devolver os funcionários que nunca receberam um prêmio, usaremos esta consulta:

SELECT 
  lastname, 
  firstname
FROM employees e1
WHERE NOT EXISTS (SELECT ph.lastname 
                  FROM payment_history ph 
                  WHERE ph.emp_id = e1.employee_id 
                  AND ph.payment_type =’award’)

Exemplo 6: Uma subconsulta correlata SQL usando NÃO EXISTÊNCIAS

EXISTS é um operador unário. Tem apenas um operando, que é uma subquadra (correlacionado ou não). Se a subconsulta retornar pelo menos um registro, então EXISTS retorna TRUE. Se a subconsulta não retorna nenhum registro, EXISTS retorna FALSE. Neste caso, você deve usar uma subconsulta correlata para obter seus resultados.

Utilização de subconsultas correlatas em UPDATE ou DELETE Statement

s vezes encontramos subconsultas correlatas em declarações UPDATE ou DELETE. O seguinte UPDATE tem uma subconsulta correlata SQL que obtém o novo valor da coluna all_money_made:

UPDATE employee emp
SET all_money_made = (SELECT SUM(payment)
                      FROM payment_history 
                      WHERE employee_id = emp.emp_id)

Exemplo 7: Uma subconsulta correlacionada a SQL em uma UPDATE

Este artigo nos mostrou quando usar uma subconsulta correlata em SQL. As consultas que procuram por negativos são bons candidatos, embora haja outras ocasiões em que uma correlação é a única opção real. Também já vimos quantas vezes uma subconsulta correlata é executada - geralmente muitas, muitas vezes. Esta é sua maior desvantagem.

Experimente você mesmo

Assubconsultas relacionadas são um recurso importante para o desenvolvedor do SQL. Para aprender mais e melhorar suas habilidades de subconsultas, tente LearnSQL.com.br's SQL para Iniciantes curso. Temos uma seção específica para subconsultas, além de muitos exercícios e exemplos!