Como continuação do artigo Como fazer subconsultas: um passo a passo, hoje iremos tratar sobre outros
tipos de subconsultas. São elas: Subconsultas de várias linhas, subconsultas de várias colunas,
subconsultas correlacionadas e, não menos importante, subconsultas aninhadas.

1. Subconsultas de várias linhas

Uma subconsulta de várias linhas retorna uma ou mais linhas para uma instrução SQL externa. Para tratar
de uma subconsulta que retorna várias linhas, sua consulta externa pode usar o operador IN, ANY ou ALL.

Podemos usar esses operadores para verificar se o valor de uma coluna está contido em uma lista de
valores, por exemplo:

select product_id, name
from products
where product_id IN (1,2,3);

1.1. Usando IN em uma subconsulta

Usamos IN para verificar se um valor está em uma lista de valores especificada. A lista de valores pode vir dos resultados retornados por uma subconsulta.

Podemos também usar NOT IN para executar a lógica oposta de IN: verificar se um valor não está em uma lista de valores especificada.

O exemplo simples a seguir usa IN para verificar se um valor de product_id está na lista de valores retornada
pela subconsulta; a subconsulta retorna o valor de product_id dos produtos cujo nome contém a letra “e”:

select product_id, name
from products
where product_id IN (select product_id
from products
where name like ‘%e%’);

O exemplo abaixo utiliza NOT IN

select product_id, name
from products
where product_id NOT IN (select product_id from purchases);

1.2. Usando ANY em uma subconsulta de várias linhas

O operador ANY é usado para comparar um valor com qualquer valor presente em uma lista. Você deve
colocar um operador =, <>, <, >, <= ou >= antes de ANY em sua consulta.

O exemplo a seguir usa ANY para obter os funcionários cujo salário é menor do que qualquer um dos salários mais baixos da tabela

salary_grades
select employee_id, last_name
from employees
where salary < ANY (select low_salary
from salary_grades);

1.3. Usando ALL em uma subconsulta de várias linhas

O operador ALL é usado para comparar um valor com todos os valores presentes em uma lista. Você deve
colocar um operador =, <>, <, >, <= ou >= antes de ALL em uma consulta. O exemplo a seguir usa esse
operador para obter os funcionários cujo salário é maior do que todos os salários mais altos da tabela

salary_grades:
select employee_id, last_name
from employees
where salary > ALL (

select high_salary
from salary_grades
);

 

Nenhum funcionário tem salário maior do que o salário mais alto.

2. Escrevendo subconsultas de várias colunas

As subconsultas vistas até aqui retornaram linhas contendo apenas uma coluna. Não estamos limitados a
uma única coluna: é possível escrever subconsultas que retornam várias colunas.

O exemplo a seguir recupera os produtos com o menor preço para cada grupo de tipo de produto:

select product_id, product_type_id, name, price
from products

where (product_type_id, price) IN (select product_type_id, MIN(price)

from products
group by product_type_id);

 

Notemos que a subconsulta retorna o valor de product_type_id e o valor de price mínimo para cada grupo de
produtos e que eles são comparados com os valores de product_type_id e price para cada produto na
cláusula WHERE da consulta externa.

3. Escrevendo subconsultas correlacionadas

Uma subconsulta correlacionada referencia uma ou mais colunas na instrução SQL externa. Elas são
chamadas de subconsultas correlacionadas, porque são relacionadas à instrução SQL externa por meio das
mesmas colunas.

Normalmente, usamos uma subconsulta correlacionada quando precisa de uma resposta para uma pergunta
que depende de um valor em cada linha contida em uma consulta externa.

Por exemplo, talvez você queira ver se existe uma relação entre os dados, mas não se preocupa com o número de linhas retornadas pela subconsulta, isto é, quer apenas verificar se alguma linha é retornada, mas não se importa com a quantidade retornada.

Uma subconsulta correlacionada é executada uma vez para cada linha na consulta externa; isso difere da
subconsulta não correlacionada, que é executada apenas uma vez antes da execução da consulta externa.

Além disso, uma subconsulta correlacionada pode trabalhar com valores nulos.

3.1. Exemplo de subconsulta correlacionada

A subconsulta correlacionada a seguir recupera os produtos que têm preço maior do que a média para seu
tipo de produto:

select product_id, product_type_id, name, price
from products outer
where price > (

select avg(price)
from products inner
where inner.product_type_id = outer.product_type_id
);

 

Usamos o apelido outer para rotular a consulta externa e o apelido inner para a subconsulta interna.

A referência à coluna product_type_id nas partes interna e externa é o que torna a subconsulta interna
correlacionada à consulta externa.

Além disso, a subconsulta retorna uma única linha contendo o preço médio do produto.

Em uma subconsulta correlacionada, cada linha da consulta externa é passada por vez para a subconsulta.

Então, ela lê uma linha cada vez da consulta externa e a aplica na subconsulta até que todas as linhas da
consulta externa sejam processadas. Então, os resultados da consulta inteira são retornados.

No exemplo anterior, a consulta externa recupera cada linha da tabela products e passa para a consulta
interna.

Cada linha é lida pela consulta interna, a qual calcula o preço médio de cada produto onde o valor de product_type_id na consulta interna é igual ao valor de product_type_id na consulta externa.

3.2. Usando EXISTS e NOT EXISTS em uma subconsulta correlacionada

O operador EXISTS verifica a existência de linhas retornadas por uma subconsulta. Embora você possa usar
EXISTS em subconsultas não correlacionadas, em geral ele é utilizado em subconsultas correlacionadas.

O operador NOT EXISTS executa a lógica oposta de EXISTS: ele verifica se linhas não existem nos resultados
retornados por uma subconsulta.

3.2.1. Usando EXISTS em uma subconsulta correlacionada

O exemplo a seguir usa EXISTS para recuperar funcionários que gerenciam outros funcionários; observe que
não nos preocupamos com a quantidade de linhas retornadas pela subconsulta, só nos preocupamos em
saber se alguma linha é retornada

select employee_id, last_name
from employee outer
where exists (

select employee_id
from employee inner
where inner.manager_id = outer.employee_id
);

Como EXISTS apenas verifica a existência de linhas retornadas pela subconsulta; uma subconsulta não
precisa retornar uma coluna, ela pode retornar apenas um valor literal. Essa característica pode melhorar o
desempenho da sua consulta. Por exemplo, a consulta a seguir reescreve o exemplo anterior com a
subconsulta retornando o valor literal 1:

select employee_id, last_name
from employees outer
where exists (
select 1
from employees inner
where inner.manager_id = outer.employee_id
);

Desde que a subconsulta retorne uma ou mais linhas, EXISTS retornará verdadeiro; se a subconsulta não
retornar uma linha, EXISTS retornará falso.

Até aqui, não nos preocupamos com a quantidade de linhas retornadas pela subconsula, focamos apenas em se alguma linha é retornada, de modo que EXISTS retorne verdadeiro ou falso.

Como a consulta externa exige pelo menos uma coluna, o valor literal 1 é retornado pela subconsulta no exemplo anterior.

3.2.2. Usando NOT EXISTS em uma coluna correlacionada

O exemplo abaixo usa NOT EXISTS para retornar os produtos que não foram comprados.

select product_id, name
from products outer
where not exists (
select 1
from purchases inner
where inner.product_id = outer.product_id
);

 

4. Escrevendo subconsultas aninhadas

Podemos aninhar subconsultas dentro de outras subconsultas até uma profundidade de 255.

Essa técnica deve ser usada com moderação, podemos verificar que sua consulta tem desempenho melhor usando
junções de tabela.

O exemplo a seguir contém uma subconsulta aninhada; observe que ela está contida dentro de uma subconsulta, que por sua vez está contida em uma consulta externa:

select product_type_id, avg(price)
from products
group by product_type_id
having avg(price) < (select max(avg(price))

from products
where product_type_id in (select product_id
from purchases
where quantity > 1)

group by product_type_id)

order by product_type_id;

Vamos entender agora o que cada passo dessa consulta faz:

select product_id
from purchases
where quantity > 1

Essa subconsulta retorna o valor de product_id dos produtos que foram comprados mais de uma vez. Os
resultados são product_id = 1 e 2.
A subconsulta que recebe essa saída é:

select max(avg(price))
from products
where product_type_id in
— saída da subconsulta aninhada
group by product_type_id;

Essa consulta retorna o maior preço médio dos produtos retornados pela subconsulta aninhada. O resultado
é 26.22
Essa linha é retornada para a seguinte consulta externa:

select product_type_id, avg(price)
from products
group by product_type_id
having avg(price) <
— saída da subconsulta
order by product_type_id;

Por fim, essa consulta retorna o valor de product_type_id e o preço médio dos produtos que são menores do
que o preço médio retornado pela subconsulta. As linhas retornadas são as que vimos no começo da seção.

Falamos bastante sobre subconsultas nesse artigo. No próximo falaremos sobre Consultas Avançadas. Até
lá!