Subconsultas SQL

O artigo descreve o que é uma subconsulta e como são estas declarações úteis. Cobriremos exemplos básicos com os operadores IN, EXISTS, QUALQUER, e TODOS os operadores, examinaremos as subconsultas nas cláusulas FROM e WHERE, e exploraremos a diferença entre as subconsultas correlatas e as subconsultas aninhadas.

Primeiro, vamos começar com um banco de dados de exemplo. Para apresentar algumas dessas declarações, precisamos ter uma tabela de exemplo e preenchê-la com alguns dados.

O que é uma Subquery?

Uma subconsulta é uma instrução SELECT com outra instrução SQL, como no exemplo abaixo.

SELECT *
FROM product
WHERE id IN (
  SELECT product_id
  FROM provider_offer
  WHERE provider_id = 156
);

As subconsultas são ainda classificadas como uma subconsulta correlacionada ou uma subconsulta aninhada. Elas são normalmente construídas de tal forma que retornam:

1. uma tabela

   SELECT MAX(average.average_price)
   FROM (
     SELECT
       product_category,
       AVG(price) AS average_price
     FROM product
     GROUP BY product_category
   ) average;
   

2. ou um valor

   SELECT id
   FROM purchase
   WHERE value > (
     SELECT AVG(value)
     FROM purchase
   );
   

Deseja saber mais sobre as Subconsultas SQL? Confira nossa série We Learn SQL no Youtube. Lembre-se de assinar o nosso canal.

Subconsultas aninhadas

Assubconsultas aninhadas são subconsultas que não dependem de uma consulta externa. Em outras palavras, ambas as consultas em uma subconsulta aninhada podem ser executadas como consultas separadas.

Este tipo de subconsulta pode ser usado em quase todos os lugares, mas normalmente é necessário um destes formatos:

SELECT
FROM
WHERE [NOT] IN (subquery)

SELECT *
FROM client
WHERE city IN (
  SELECT city
  FROM provider
);

A subconsulta de exemplo devolve todos os clientes que são da mesma cidade que os fornecedores do produto.
O operador IN verifica se o valor está dentro da tabela e recupera as linhas correspondentes.

SELECT
FROM
WHERE expression comparison_operator [ANY| ALL] (subquery)

Subconquista com TODO Operador

O operador TODO compara um valor a cada valor da tabela de resultados.

Por exemplo, a consulta a seguir retorna todos os modelos e produtores de bicicletas que têm um preço maior do que os fones de ouvido mais caros.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ALL(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Subconsulta semelhante, mas com QUALQUER operadora:

Subconsulta com QUALQUER Operador

O QUALQUER operador compara um valor a cada valor em uma tabela e avalia se o resultado de uma consulta interna contém ou não pelo menos uma linha.

A consulta a seguir retorna todos os modelos e produtores de bicicletas que têm um preço superior a pelo menos um dos fones de ouvido.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ANY(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Você também pode aninhar uma subconsulta em outra subconsulta. Por exemplo:

Subconsulta aninhada em outra subconsulta usando IN Operator

Esta consulta devolve os produtores e modelos de bicicletas que existem nas ofertas dos fornecedores dos EUA.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND id IN (
    SELECT distinct product_id
    FROM provider_offer
    WHERE provider_id IN (
      SELECT id
      FROM provider
      WHERE country = 'USA'
    )
  );

O mesmo poderia ser feito através de adesões.

SELECT product.producer, product.model
FROM product, provider_offer, provider
WHERE provider_offer.product_id = product.id
  AND provider_offer.provider_id = provider.id
  AND product_category = 'bike'
  AND provider.country = 'USA';

Subconsultas relacionadas

Assubconsultas são correlacionadas quando as consultas internas e externas são interdependentes, ou seja, quando a consulta externa é uma consulta que contém uma subconsulta e a própria subconsulta é uma consulta interna. Os usuários que conhecem conceitos de programação podem compará-la a uma estrutura de loop aninhado.

Vamos começar com um exemplo simples.

A consulta interna calcula o valor médio e o devolve. Na cláusula WHERE da consulta externa, filtramos apenas aquelas compras que têm um valor maior do que o valor retornado da consulta interna.

Subquery Correlated in WHERE Cláusula

SELECT id
FROM purchase p1
WHERE date > '2013-07-15'
  AND value > (
    SELECT AVG(value)
    FROM purchase p2
    WHERE p1.date = p2.date
  );

A consulta retorna as compras após 15/07/2014 com um preço total maior que o valor médio do mesmo dia.

O exemplo equivalente, mas com a união de tabelas.

SELECT  p1.id
FROM purchase p1, purchase p2
WHERE p1.date = p2.date
  AND p1.date > '2013-07-15'
GROUP BY p1.id
HAVING p1.value > AVG(p2.value);

Este exemplo também pode ser escrito como uma declaração SELECT com uma subconsulta correlacionada em uma cláusula FROM.

A subconsulta retorna a tabela que contém o valor médio de cada compra para cada dia. Juntamos este resultado com a tabela de compras na coluna 'data' para verificar a data da condição > '15/07/2014′'.

SELECT id
FROM
  purchase,
  (
    SELECT date, AVG(value) AS average_value
    FROM purchase
    WHERE date > '2013-07-15'
    GROUP BY date
  ) average
WHERE purchase.date  = average.date
  AND purchase.date  > '2013-07-15'
  AND purchase.value > average.average_value;

Normalmente, este tipo de subconsulta deve ser evitado porque os índices não podem ser usados em uma tabela temporária em memória.

Subconsulta com EXISTÊNCIAS

SELECT
FROM
WHERE [NOT] EXISTS (subquery)

O operador EXISTS verifica se a fila da subconsulta corresponde a alguma fila da consulta externa. Se não houver dados correspondentes, o operador EXISTS retorna FALSO.

Esta Consulta retorna todos os clientes que encomendaram após 10/07/2013.

SELECT id, company_name
FROM client
WHERE EXISTS(
  SELECT *
  FROM purchase
  WHERE client.id = purchase.client_id
  WHERE date > '2013-07-10'
);

Quando uma subconsulta é usada, o otimizador de consulta realiza etapas adicionais antes que os resultados da subconsulta sejam usados. Se uma consulta que contém uma subconsulta pode ser escrita usando um join, ela deve ser feita desta forma. As uniões geralmente permitem que o otimizador de consultas recupere os dados de uma maneira mais eficiente.

Limitações da subconsulta

Há algumas limitações no uso de subconsultas:

• No Oracle você pode aninhar até 255 níveis de subconsultas em uma cláusula WHERE.
• No SQL Server, você pode aninhar até 32 níveis.