Subconsultas (SQL Server)

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Sistema de Plataforma de Análise (PDW)Base de dados SQL no Microsoft Fabric

Uma subconsulta é uma consulta aninhada dentro de uma instrução SELECT, INSERT, UPDATE ou DELETE, ou dentro de outra subconsulta.

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que pode ser descarregado da página inicial de Exemplos e Projetos da Comunidade do Microsoft SQL Server.

Uma subconsulta pode ser usada em qualquer lugar onde uma expressão é permitida. Neste exemplo, uma subconsulta é usada como uma expressão de coluna chamada MaxUnitPrice numa instrução SELECT.

USE AdventureWorks2022;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO

Fundamentos da subconsulta

Uma subconsulta também é chamada de consulta interna ou seleção interna, enquanto a instrução que contém uma subconsulta também é chamada de consulta externa ou seleção externa.

Muitas Transact-SQL instruções que incluem subconsultas podem ser formuladas alternativamente como junções. Outras perguntas só podem ser feitas com subconsultas. No Transact-SQL, geralmente não há diferença de desempenho entre uma instrução que inclui uma subconsulta e uma versão semanticamente equivalente que não inclui. Para obter informações de arquitetura sobre como o SQL Server processa consultas, consulte Processamento de instruções SQL. No entanto, em alguns casos em que a existência deve ser verificada, uma junção produz um melhor desempenho. Caso contrário, a consulta aninhada deve ser processada para cada resultado da consulta externa para garantir a eliminação de duplicatas. Nesses casos, uma abordagem conjunta produziria melhores resultados.

O exemplo a seguir mostra uma subconsulta SELECT e uma junção SELECT que retornam o mesmo conjunto de resultados e plano de execução:

USE AdventureWorks2022;
GO

/* SELECT statement built using a subquery. */
SELECT [Name]
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE [Name] = 'Chainring Bolts' );
GO

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1.[Name]
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.[Name] = 'Chainring Bolts';
GO

Uma subconsulta aninhada na instrução externa SELECT tem os seguintes componentes:

Uma consulta regular SELECT , incluindo os componentes regulares da lista de seleção.
Uma cláusula regular FROM que inclui um ou mais nomes de tabelas ou vistas.
Cláusula opcional WHERE .
Cláusula opcional GROUP BY .
Cláusula opcional HAVING .

A SELECT consulta de uma subconsulta é sempre colocada entre parênteses. Ele não pode incluir uma COMPUTE cláusula ou FOR BROWSE e só pode incluir uma ORDER BY cláusula quando uma TOP cláusula também é especificada.

Uma subconsulta pode ser aninhada dentro da cláusula WHERE ou HAVING de uma instrução SELECT, INSERT, UPDATE ou DELETE externa, ou dentro de outra subconsulta. É possível até 32 níveis de aninhamento, embora o limite varie com base na memória disponível e na complexidade de outras expressões na consulta. As consultas individuais não suportam aninhamento com um máximo de 32 níveis. Uma subconsulta pode aparecer em qualquer lugar onde uma expressão possa ser usada, se ela retornar um único valor.

Se uma tabela aparecer apenas em uma subconsulta e não na consulta externa, as colunas dessa tabela não poderão ser incluídas na saída (a lista de seleção da consulta externa).

As instruções que incluem uma subconsulta geralmente usam um destes formatos:

WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL] (subquery)
WHERE [NOT] EXISTS (subquery)

Em algumas instruções Transact-SQL, a subconsulta pode ser avaliada como se fosse uma consulta independente. Conceitualmente, os resultados da subconsulta são substituídos na consulta externa (embora isso não seja necessariamente como o SQL Server realmente processa instruções Transact-SQL com subconsultas).

Existem três tipos básicos de subconsultas. Aqueles que:

Operar em listas introduzidas com IN, ou aquelas que um operador de comparação modificou por ANY ou ALL.
São introduzidos com um operador de comparação não modificado e devem retornar um único valor.
São testes de existência introduzidos com EXISTS.

Regras de subconsulta

Uma subconsulta está sujeita às seguintes restrições:

A lista de seleção de uma subconsulta introduzida com um operador de comparação pode incluir apenas uma expressão ou nome de coluna (exceto que EXISTS e IN operam em SELECT * ou numa lista, respetivamente).
Se a WHERE cláusula de uma consulta externa incluir um nome de coluna, ela deverá ser compatível com a coluna na lista de seleção de subconsulta.
Os tipos de dados ntext, text e image não podem ser usados na lista selecionada de subconsultas.
Como eles devem retornar um único valor, as subconsultas introduzidas por um operador de comparação não modificado (um não seguido pela palavra-chave ANY ou ALL) não podem incluir GROUP BY e HAVING cláusulas.
A DISTINCT palavra-chave não pode ser usada com subconsultas que incluam GROUP BY.
As cláusulas COMPUTE e INTO não podem ser especificadas.
ORDER BY só pode ser especificado quando TOP também é especificado.
Um modo de exibição criado usando uma subconsulta não pode ser atualizado.
A lista de seleção de uma subconsulta introduzida com EXISTS, por convenção, tem um asterisco (*) em vez de um único nome de coluna. As regras para uma subconsulta introduzida com EXISTS são as mesmas de uma lista de seleção padrão, porque uma subconsulta introduzida com EXISTS cria um teste de existência e retorna TRUE ou FALSE, em vez de dados.

Qualificar nomes de colunas em subconsultas

No exemplo a seguir, a BusinessEntityID coluna na WHERE cláusula da consulta externa é implicitamente qualificada pelo nome da tabela na cláusula de consulta FROM externa (Sales.Store). A referência na lista de seleção da subconsulta é qualificada pela cláusula da subconsulta CustomerID, ou seja, pela tabela FROM.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

A regra geral é que os nomes das colunas num comando são implicitamente associados pela tabela referenciada na cláusula FROM, ao mesmo nível. Se uma coluna não existir na tabela referenciada na FROM cláusula de uma subconsulta, ela será implicitamente qualificada pela tabela referenciada na FROM cláusula da consulta externa.

Veja como a consulta se parece com essas suposições implícitas especificadas:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
    (SELECT Sales.Customer.CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

Nunca é errado declarar o nome da tabela explicitamente, e é sempre possível substituir suposições implícitas sobre nomes de tabelas com qualificações explícitas.

Important

Se uma coluna for referenciada em uma subconsulta que não existe na tabela referenciada pela cláusula da FROM subconsulta, mas existe em uma tabela referenciada pela cláusula da FROM consulta externa, a consulta será executada sem erros. O SQL Server qualifica implicitamente a coluna na subconsulta com o nome da tabela na consulta externa.

Vários níveis de aninhamento

Uma subconsulta pode incluir uma ou mais subconsultas. Qualquer número de subconsultas pode ser aninhado em uma instrução.

A consulta a seguir localiza os nomes dos funcionários que também são vendedores.

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM HumanResources.Employee
     WHERE BusinessEntityID IN
        (SELECT BusinessEntityID
         FROM Sales.SalesPerson)
    );
GO

Aqui está o conjunto de resultados.

LastName                                           FirstName
-------------------------------------------------- -----------------------
Jiang                                              Stephen
Abbas                                              Syed
Alberts                                            Amy
Ansman-Wolfe                                       Pamela
Campbell                                           David
Carson                                             Jillian
Ito                                                Shu
Mitchell                                           Linda
Reiter                                             Tsvi
Saraiva                                            Jos
Vargas                                             Garrett
Varkey Chudukatil                                  Ranjit
Valdez                                             Rachel
Tsoflias                                           Lynn
Pak                                                Jae
Blythe                                             Michael
Mensa-Annan                                        Tete

(17 row(s) affected)

A consulta mais interna retorna os IDs do vendedor. A consulta no próximo nível superior é avaliada com esses IDs de vendedor e retorna os números de ID de contato dos funcionários. Finalmente, a consulta externa usa os IDs de contato para encontrar os nomes dos funcionários.

Você também pode expressar esta consulta como um join.

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
ON c.BusinessEntityID = e.BusinessEntityID
JOIN Sales.SalesPerson s
ON e.BusinessEntityID = s.BusinessEntityID;
GO

Subconsultas correlacionadas

Muitas consultas podem ser avaliadas executando a subconsulta uma vez e substituindo o(s) valor(es) resultante(s) na WHERE cláusula da consulta externa. Em consultas que incluem uma subconsulta correlacionada (também conhecida como subconsulta de repetição), a subconsulta depende da consulta externa para seus valores. Isso significa que a subconsulta é executada repetidamente, uma vez para cada linha que pode ser selecionada pela consulta externa.

Esta consulta recupera uma instância de cada nome e sobrenome de funcionário para o qual o bônus na tabela SalesPerson é de 5000 e cujos números de identificação do funcionário correspondem nas tabelas Employee e SalesPerson.

USE AdventureWorks2022;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

Aqui está o conjunto de resultados.

LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282

(2 row(s) affected)

A subconsulta anterior nesta instrução não pode ser avaliada de forma independente da consulta principal. Ele precisa de um valor para Employee.BusinessEntityID, mas esse valor muda à medida que o SQL Server examina linhas diferentes no Employee. É exatamente assim que a consulta é avaliada: o SQL Server considera cada linha da tabela Employee para inclusão nos resultados, substituindo o valor de cada linha na consulta interna. Por exemplo, se o SQL Server primeiro examinar a linha para Syed Abbas, a variável Employee.BusinessEntityID assume o valor 285, que o SQL Server substitui na consulta interna. Esses dois exemplos de consulta representam uma decomposição do exemplo anterior com a subconsulta correlacionada.

USE AdventureWorks2022;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO

O resultado é 0,00 (Syed Abbas não recebeu um bônus porque não é um vendedor), portanto, a consulta externa avalia-se como:

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000 IN (0.00);
GO

Como isso é falso, a linha para Syed Abbas não é incluída nos resultados da consulta de exemplo anterior com a subconsulta correlacionada. Siga o mesmo procedimento com a linha de Pamela Ansman-Wolfe. Você vê que essa linha está incluída nos resultados, porque WHERE 5000 IN (5000) inclui resultados.

As subconsultas correlacionadas também podem incluir funções com valor de tabela na cláusula, FROM fazendo referência a colunas de uma tabela na consulta externa como um argumento da função com valor de tabela. Nesse caso, para cada linha da consulta externa, a função com valor de tabela é avaliada de acordo com a subconsulta.

Tipos de subconsulta

As subconsultas podem ser especificadas em muitos locais:

Com pseudónimos. Para obter mais informações, consulte Subconsultas com aliases de tabela.
Com IN ou NOT IN. Para obter mais informações, consulte Subconsultas com a cláusula IN e Subconsultas com a cláusula NOT IN.
Em UPDATE, DELETE, e INSERT declarações. Para mais informações, consulte Subconsultas nas instruções UPDATE, DELETE e INSERT.
Com operadores de comparação. Para obter mais informações, consulte Subconsultas com operadores de comparação.
Com ANY, SOME, ou ALL. Para obter mais informações, consulte Operadores de comparação modificados por ANY, SOME ou ALL.
Com IS [NOT] DISTINCT FROM. Para obter mais informações, consulte É [NÃO] DISTINTO DE (Transact-SQL).
Com EXISTS ou NOT EXISTS. Para obter mais informações, consulte Subconsultas com EXISTS e Subconsultas com NOT EXISTS.
No lugar de uma expressão. Para obter mais informações, consulte Subconsultas usadas no lugar de uma expressão.

Subconsultas com aliases de tabela

Muitas instruções nas quais a subconsulta e a consulta externa se referem à mesma tabela podem ser realizadas como autojunções (ligando uma tabela a si mesma). Por exemplo, você pode encontrar endereços de funcionários de um determinado estado usando uma subconsulta:

USE AdventureWorks2022;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
    (SELECT AddressID
     FROM Person.Address
     WHERE StateProvinceID = 39);
GO

Aqui está o conjunto de resultados.

StateProvinceID AddressID
----------- -----------
39 942
39 955
39 972
39 22660

(4 row(s) affected)

Ou podes usar uma auto-junção:

USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
INNER JOIN Person.Address AS e2
ON e1.AddressID = e2.AddressID
AND e2.StateProvinceID = 39;
GO

Aliases de e1 tabela e e2 são necessários porque a tabela que está sendo unida a si mesma aparece em duas funções diferentes. Os aliases também podem ser usados em consultas aninhadas que se referem à mesma tabela em uma consulta interna e externa.

USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
WHERE e1.AddressID IN
    (SELECT e2.AddressID
     FROM Person.Address AS e2
     WHERE e2.StateProvinceID = 39);
GO

Os aliases explícitos de tabela deixam claro que uma referência a Person.Address na subconsulta não tem o mesmo significado que a referência na consulta externa.

Subconsultas com IN

O resultado de uma subconsulta introduzida com IN (ou com NOT IN) é uma lista de zero ou mais valores. Depois que a subconsulta retorna resultados, a consulta externa faz uso deles. A consulta a seguir encontra os nomes de todos os produtos de roda que a Adventure Works Cycles faz.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Aqui está o conjunto de resultados.

Name
----------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

Esta declaração é avaliada em duas etapas. Primeiro, a consulta interna retorna o número de identificação da subcategoria que corresponde ao nome Wheel (17). Em segundo lugar, esse valor é substituído na consulta externa, que localiza os nomes de produtos que acompanham os números de identificação da subcategoria em Production.Product.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO

Uma diferença no uso de uma junção em vez de uma subconsulta para este e problemas semelhantes é que a junção permite mostrar colunas de mais de uma tabela no resultado. Por exemplo, se você quiser incluir o nome da subcategoria do produto no resultado, deverá usar uma versão de associação.

USE AdventureWorks2022;
GO
SELECT p.[Name], s.[Name]
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.[Name] = 'Wheels';
GO

Aqui está o conjunto de resultados.

Name
LL Mountain Front Wheel Wheels
ML Mountain Front Wheel Wheels
HL Mountain Front Wheel Wheels
LL Road Front Wheel Wheels
ML Road Front Wheel Wheels
HL Road Front Wheel Wheels
Touring Front Wheel Wheels
LL Mountain Rear Wheel Wheels
ML Mountain Rear Wheel Wheels
HL Mountain Rear Wheel Wheels
LL Road Rear Wheel Wheels
ML Road Rear Wheel Wheels
HL Road Rear Wheel Wheels
Touring Rear Wheel Wheels

(14 row(s) affected)

A consulta a seguir localiza o nome de todos os fornecedores cuja classificação de crédito é boa, dos quais a Adventure Works Cycles encomenda pelo menos 20 itens e cujo prazo médio de entrega é inferior a 16 dias.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM Purchasing.ProductVendor
     WHERE MinOrderQty >= 20
     AND AverageLeadTime < 16);
GO

Aqui está o conjunto de resultados.

Name
--------------------------------------------------
Compete Enterprises, Inc
International Trek Center
First National Sport Co.
Comfort Road Bicycles
Circuit Cycles
First Rate Bicycles
Jeff's Sporting Goods
Competition Bike Training Systems
Electronic Bike Repair & Supplies
Crowley Sport
Expert Bike Co
Team Athletic Co.
Compete, Inc.

(13 row(s) affected)

A consulta interna é avaliada, produzindo os números de ID dos fornecedores que atendem às qualificações da subconsulta. A consulta externa é então avaliada. Você pode incluir mais de uma condição na WHERE cláusula da consulta interna e externa.

Usando uma junção, a mesma consulta é expressa assim:

USE AdventureWorks2022;
GO
SELECT DISTINCT [Name]
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.BusinessEntityID = p.BusinessEntityID
WHERE CreditRating = 1
  AND MinOrderQty >= 20
  AND AverageLeadTime < 16;
GO

Uma junção sempre pode ser expressa como uma subconsulta. Uma subconsulta pode muitas vezes, mas nem sempre, ser expressa como uma junção. Isso ocorre porque as junções são simétricas: você pode unir a tabela A em B qualquer ordem e obter a mesma resposta. O mesmo não acontece se uma subconsulta estiver envolvida.

Subconsultas com NOT IN

As subconsultas introduzidas com a palavra-chave NOT IN também retornam uma lista de zero ou mais valores. A consulta a seguir localiza os nomes dos produtos que não são bicicletas acabadas.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Mountain Bikes'
        OR [Name] = 'Road Bikes'
        OR [Name] = 'Touring Bikes');
GO

Esta instrução não pode ser convertida em uma junção. A junção análoga não igual tem um significado diferente: encontra os nomes de produtos que estão em alguma subcategoria que não é uma bicicleta acabada.

Subconsultas nas instruções UPDATE, DELETE e INSERT

As subconsultas podem ser aninhadas nas instruções UPDATE, DELETE, INSERT e SELECT de manipulação de dados (DML).

O exemplo a seguir dobra o valor na coluna ListPrice na tabela Production.Product. A subconsulta na cláusula faz referência à tabela WHEREPurchasing.ProductVendor para restringir as linhas atualizadas na tabela Produto apenas àquelas fornecidas por BusinessEntity1540.

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

Aqui está uma instrução equivalente UPDATE usando um JOIN:

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO

Para maior clareza, caso a mesma tabela seja referenciada em outras subconsultas, use o alias da tabela de destino:

USE AdventureWorks2022;
GO
UPDATE p
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO

Subconsultas com operadores de comparação

As subconsultas podem ser introduzidas com um dos operadores de comparação (=, < >, >, > =, <, ! >! <, ou < =).

Uma subconsulta introduzida com um operador de comparação não modificado (um operador de comparação não seguido por ANY ou ALL) deve retornar um único valor em vez de uma lista de valores, como subconsultas introduzidas com IN. Se essa subconsulta retornar mais de um valor, o SQL Server exibirá uma mensagem de erro.

Para usar uma subconsulta introduzida com um operador de comparação não modificado, você deve estar familiarizado o suficiente com seus dados e com a natureza do problema para saber que a subconsulta retornará exatamente um valor.

Por exemplo, se você assumir que cada vendedor cobre apenas um território de vendas e quiser encontrar os clientes localizados no território coberto pelo Linda Mitchell, poderá escrever uma declaração com uma subconsulta introduzida com o operador de comparação simples = .

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
    (SELECT TerritoryID
     FROM Sales.SalesPerson
     WHERE BusinessEntityID = 276);
GO

Se, no entanto, Linda Mitchell abrangesse mais do que um território de vendas, daí resultaria uma mensagem de erro. Em vez de utilizar o operador de comparação =, poderia-se usar a formulação IN (=ANY também funciona).

As subconsultas introduzidas com operadores de comparação não modificados geralmente incluem funções agregadas, porque elas retornam um único valor. Por exemplo, a instrução a seguir localiza os nomes de todos os produtos cujo preço de tabela é maior do que o preço de tabela médio.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product);
GO

Como as subconsultas introduzidas com operadores de comparação não modificados devem retornar um único valor, elas não podem incluir GROUP BY ou HAVING cláusulas, a menos que esteja garantido que a cláusula GROUP BY ou HAVING por si só retorne um único valor. Por exemplo, a consulta a seguir localiza os produtos com preço mais alto do que o produto com preço mais baixo que está no ProductSubcategoryID14.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT MIN (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID
     HAVING ProductSubcategoryID = 14);
GO

Operadores de comparação modificados por `ANY`, `SOME`ou `ALL`

Os operadores de comparação que introduzem uma subconsulta podem ser modificados pelas palavras-chave ALL ou ANY. SOME é um equivalente da norma ISO para ANY. Para obter mais informações sobre esses operadores de comparação, consulte ALGUNS | QUALQUER.

As subconsultas introduzidas com um operador de comparação modificado retornam uma lista de zero ou mais valores e podem incluir uma cláusula GROUP BY ou HAVING. Essas subconsultas podem ser reafirmadas com EXISTS.

Usando o > operador de comparação como exemplo, > ALL significa maior do que cada valor. Em outras palavras, significa maior do que o valor máximo. Por exemplo, > ALL (1, 2, 3) significa maior que 3. > ANY significa maior que pelo menos um valor, ou seja, maior que o mínimo. Portanto, > ANY (1, 2, 3) significa maior que 1.

Para que uma linha em uma subconsulta satisfaça > ALL a condição especificada na consulta externa, o valor na coluna que introduz a subconsulta deve ser maior do que cada valor na lista de valores retornados pela subconsulta.

Da mesma forma, significa que, > ANY para que uma linha satisfaça a condição especificada na consulta externa, o valor na coluna que introduz a subconsulta deve ser maior do que pelo menos um dos valores na lista de valores retornados pela subconsulta.

A consulta a seguir fornece um exemplo de uma subconsulta introduzida com um operador de comparação modificado pelo ANY. Ele encontra os produtos cujos preços de tabela são maiores ou iguais ao preço de tabela máximo de qualquer subcategoria de produto.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID);
GO

Para cada subcategoria de Produto, a consulta interna localiza o preço de tabela máximo. A consulta externa examina todos esses valores e determina quais preços de tabela de produtos individuais são maiores ou iguais ao preço de tabela máximo de qualquer subcategoria de produto. Se ANY for alterada para ALL, a consulta retornará somente os produtos cujo preço de tabela é maior ou igual a todos os preços de tabela retornados na consulta interna.

Se a subconsulta não retornar nenhum valor, a consulta inteira não retornará nenhum valor.

O = ANY operador é equivalente a IN. Por exemplo, para encontrar os nomes de todos os produtos de rodas que a Adventure Works Cycles fabrica, pode-se usar ou IN ou = ANY.

--Using = ANY
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID = ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

--Using IN
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

Aqui está o conjunto de resultados para uma ou outra consulta:

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

O <> ANY operador, no entanto, difere de NOT IN:

<> ANY significa não = a, ou não = b, ou não = c
NOT IN significa não = a, e não = b, e não = c
<> ALL significa o mesmo que NOT IN

Por exemplo, a consulta a seguir encontra clientes localizados em um território não coberto por nenhum vendedor.

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson);
GO

Os resultados incluem todos os clientes, exceto aqueles cujos territórios de vendas são NULL, porque cada território atribuído a um cliente é coberto por um vendedor. A consulta interna localiza todos os territórios de vendas cobertos por vendedores e, em seguida, para cada território, a consulta externa encontra os clientes que não estão em um.

Pela mesma razão, quando você usa NOT IN nesta consulta, os resultados não incluem nenhum dos clientes.

Você pode obter os mesmos resultados com o operador <> ALL, que é equivalente a NOT IN.

Subconsultas com `EXISTS`

Quando uma subconsulta é introduzida com a palavra-chave EXISTS, a subconsulta funciona como um teste de existência. A WHERE cláusula da consulta externa testa se as linhas retornadas pela subconsulta existem. Na verdade, a subconsulta não produz dados; ele retorna um valor de TRUE ou FALSE.

Uma subconsulta introduzida com EXISTS tem a seguinte sintaxe: WHERE [NOT] EXISTS (subquery)

A consulta a seguir localiza os nomes de todos os produtos que estão na subcategoria Rodas:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Aqui está o conjunto de resultados.

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

Para entender os resultados dessa consulta, considere o nome de cada produto por vez. Esse valor faz com que a subconsulta retorne pelo menos uma linha? Em outras palavras, a consulta faz com que o teste de existência resulte em TRUE?

As subconsultas que são introduzidas com EXISTS são um pouco diferentes de outras subconsultas das seguintes maneiras:

A palavra-chave EXISTS não é precedida por um nome de coluna, constante ou outra expressão.
A lista de seleção de uma subconsulta introduzida por EXISTS quase sempre consiste em um asterisco (*). Não há razão para listar nomes de colunas porque você está apenas testando se existem linhas que atendem às condições especificadas na subconsulta.

A palavra-chave EXISTS é importante porque frequentemente não existe uma formulação alternativa sem subconsultas. Embora algumas consultas criadas com EXISTS não possam ser expressas de outra forma, muitas consultas podem usar IN ou um operador de comparação modificado por ANY ou ALL para obter resultados semelhantes.

Por exemplo, a consulta anterior pode ser expressa usando IN:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Subconsultas com `NOT EXISTS`

NOT EXISTS funciona como EXISTS, exceto que a cláusula WHERE é satisfeita quando nenhuma linha é retornada pela subconsulta.

Por exemplo, para encontrar os nomes dos produtos que não estão na subcategoria de rodas:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Subconsultas usadas no lugar de uma expressão

No Transact-SQL, uma subconsulta pode ser substituída em qualquer lugar onde uma expressão possa ser usada em SELECT, UPDATE, INSERT, e DELETE instruções, exceto numa lista de ORDER BY.

O exemplo a seguir ilustra como você pode usar esse aprimoramento. Esta consulta encontra os preços de todos os produtos de BTT, o seu preço médio e a diferença entre o preço de cada bicicleta de montanha e o preço médio.

USE AdventureWorks2022;
GO
SELECT [Name], ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1;
GO

Feedback

Esta página foi útil?

Last updated on 2025-11-18

Partilhar via

Subconsultas (SQL Server)

Fundamentos da subconsulta

Regras de subconsulta

Qualificar nomes de colunas em subconsultas

Vários níveis de aninhamento

Subconsultas correlacionadas

Tipos de subconsulta

Subconsultas com aliases de tabela

Subconsultas com IN

Subconsultas com NOT IN

Subconsultas nas instruções UPDATE, DELETE e INSERT

Subconsultas com operadores de comparação

Operadores de comparação modificados por ANY, SOMEou ALL

Subconsultas com EXISTS

Subconsultas com NOT EXISTS

Subconsultas usadas no lugar de uma expressão

Conteúdo relacionado

Feedback

Recursos adicionais

Operadores de comparação modificados por `ANY`, `SOME`ou `ALL`

Subconsultas com `EXISTS`

Subconsultas com `NOT EXISTS`