Вложенные запросы (SQL Server)Subqueries (SQL Server)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Вложенный запрос — это запрос, который используется внутри инструкции SELECT, INSERT, UPDATE или DELETE или внутри другого вложенного запроса.A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Подзапрос может быть использован везде, где разрешены выражения.A subquery can be used anywhere an expression is allowed. В данном примере вложенный запрос используется в качестве выражения для столбца MaxUnitPrice в инструкции SELECT.In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.

USE AdventureWorks2016;
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

Основы вложенных запросовSubquery Fundamentals

Вложенный запрос по-другому называют внутренним запросом или внутренней операцией выбора, в то время как инструкцию, содержащую вложенный запрос, называют внешним запросом или внешней операцией выбора.A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Многие инструкции языка Transact-SQLTransact-SQL, включающие подзапросы, можно записать в виде соединений.Many Transact-SQLTransact-SQL statements that include subqueries can be alternatively formulated as joins. Другие запросы могут быть осуществлены только с помощью подзапросов.Other questions can be posed only with subqueries. В языке Transact-SQLTransact-SQL обычно не бывает разницы в производительности между инструкцией, включающей вложенный запрос, и семантически эквивалентной версией без вложенного запроса.In Transact-SQLTransact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. Однако в некоторых случаях, когда проверяется существование, соединения показывают лучшую производительность.However, in some cases where existence must be checked, a join yields better performance. В противном случае для устранения дубликатов вложенный запрос должен обрабатываться для получения каждого результата внешнего запроса.Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. В таких случаях метод работы соединений дает лучшие результаты.In such cases, a join approach would yield better results. В следующем примере используются вложенный запрос SELECT и соединение SELECT, которые возвращают один и тот же результирующий набор:The following is an example showing both a subquery SELECT and a join SELECT that return the same result set:

USE AdventureWorks2016;
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

Вложенный во внешнюю инструкцию SELECT запрос, имеет следующие компоненты:A subquery nested in the outer SELECT statement has the following components:

  • обычный запрос SELECT, включающий обычные компоненты списка выборки;A regular SELECT query including the regular select list components.
  • обычное предложение FROM, включающее одно или несколько имен таблиц или представлений.A regular FROM clause including one or more table or view names.
  • Необязательное предложение WHERE.An optional WHERE clause.
  • Необязательное предложение GROUP BY.An optional GROUP BY clause.
  • Необязательное предложение HAVING.An optional HAVING clause.

Запрос SELECT вложенного запроса всегда заключен в скобки.The SELECT query of a subquery is always enclosed in parentheses. Он не может включать предложения COMPUTE или FOR BROWSE и может включать предложение ORDER BY только вместе с предложением TOP.It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

Вложенный запрос может быть включен в предложение WHERE или HAVING внешней инструкции SELECT, INSERT, UPDATE или DELETE или в другой вложенный запрос.A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Возможно создавать вложенность до 32-го уровня, хотя ограничения меняются в зависимости от объема доступной памяти и сложности других выражений в запросе.Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Отдельные запросы могут не поддерживать вложенность до 32-го уровня.Individual queries may not support nesting up to 32 levels. Подзапрос может появляться везде, где может использоваться выражение, если он возвращает одно значение.A subquery can appear anywhere an expression can be used, if it returns a single value.

Если таблица появляется только во вложенном запросе, а не во внешнем запросе, в этом случае столбцы данной таблицы не могут быть включены в выходные данные (список выборки внешнего запроса).If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).

Инструкции, включающие вложенные запросы, обычно имеют один из следующих форматов:Statements that include a subquery usually take one of these formats:

  • WHERE выражение [NOT] IN (вложенный запрос)WHERE expression [NOT] IN (subquery)
  • WHERE выражение оператор_сравнения [ANY | ALL] (вложенный запрос)WHERE expression comparison_operator [ANY | ALL] (subquery)
  • WHERE [NOT] EXISTS (вложенный запрос)WHERE [NOT] EXISTS (subquery)

В некоторых инструкциях языка Transact-SQLTransact-SQL вложенный запрос может рассматриваться как отдельный запрос.In some Transact-SQLTransact-SQL statements, the subquery can be evaluated as if it were an independent query. Обычно результаты вложенного запроса подставляются во внешний запрос (хотя SQL ServerSQL Server может обрабатывать инструкции Transact-SQLTransact-SQL с вложенными запросами и по-другому).Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how SQL ServerSQL Server actually processes Transact-SQLTransact-SQL statements with subqueries).

Существуют три основных типа подзапросов,There are three basic types of subqueries. которые:Those that:

  • работают в списках, указанных с помощью ключевого слова IN, или тех, которые оператор сравнения изменил с помощью ключевого слова ANY или ALL;Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
  • вставлены оператором немодифицированных сравнений и должны возвращать одно значение;Are introduced with an unmodified comparison operator and must return a single value.
  • являются проверками на существование, начинающимися с ключевого слова EXISTS.Are existence tests introduced with EXISTS.

Правила вложенных запросовSubquery rules

На вложенный запрос распространяются следующие ограничения:A subquery is subject to the following restrictions:

  • Список выбора вложенного запроса, начинающийся с оператора сравнения, может включать только одно выражение или имя столбца (за исключением операторов EXISTS и IN в инструкции SELECT * или в списке соответственно).The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
  • Если предложение WHERE внешнего запроса включает имя столбца, оно должно быть совместимо для соединения со столбцом в списке выбора вложенного запроса.If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
  • Типы данных ntext, text и image не могут быть использованы в списке выбора вложенных запросов.The ntext, text, and image data types cannot be used in the select list of subqueries.
  • Вложенные запросы, представленные оператором неизмененного сравнения (после которого нет ключевого слова ANY или ALL), не могут включать предложения GROUP BY и HAVING.Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
  • Ключевое слово DISTINCT не может быть использовано во вложенном запросе, включающем предложение GROUP BY.The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
  • Предложения COMPUTE и INTO не могут быть указаны.The COMPUTE and INTO clauses cannot be specified.
  • Предложение ORDER BY может быть указано только вместе с предложением TOP.ORDER BY can only be specified when TOP is also specified.
  • Представление, созданное с помощью вложенного запроса, не может быть обновлено.A view created by using a subquery cannot be updated.
  • Список выбора вложенного запроса, начинающегося с предложения EXISTS, по соглашению содержит звездочку (*) вместо отдельного имени столбца.The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. Правила для вложенного запроса, начинающегося с предложения EXISTS, являются такими же, как для стандартного списка выбора, поскольку вложенный запрос, начинающийся с предложения EXISTS, проводит проверку на существование и возвращает TRUE или FALSE вместо данных.The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.

Уточнение имен столбцов во вложенных запросахQualifying column names in subqueries

В следующем примере столбец BusinessEntityID в предложении WHERE внешнего запроса неявно уточняется именем таблицы, используемой в предложении FROM внешнего запроса (Sales.Store).In the following example, the BusinessEntityID column in the WHERE clause of the outer query is implicitly qualified by the table name in the outer query FROM clause (Sales.Store). Ссылка на столбец CustomerID в списке выборки вложенного запроса уточняется именем таблицы с помощью предложения FROM вложенного запроса, то есть Sales.Customer.The reference to CustomerID in the select list of the subquery is qualified by the subquery FROM clause, that is, by the Sales.Customer table.

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

Общее правило состоит в том, что имена столбцов в инструкции неявно уточняются именем таблицы, указанной в предложении FROM того же уровня вложенности.The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. Если столбец не существует в таблице, на которую ссылается предложение FROM вложенного запроса, он неявно уточняется именем таблицы, указанной в предложении FROM внешнего запроса.If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

Вот как выглядит этот запрос с явно указанными неявными соглашениями:Here is what the query looks like with these implicit assumptions specified:

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

Никогда не будет ошибочным явно указать имя таблицы; также всегда можно перекрыть неявные соглашения об именах таблиц полностью уточненными именами столбцовIt is never wrong to state the table name explicitly, and it is always possible to override implicit assumptions about table names with explicit qualifications.

Важно!

Если столбец, на который есть ссылка во вложенном запросе, не существует в таблице, указанной в предложении FROM вложенного запроса, но существует в таблице, на которую ссылается предложение FROM внешнего запроса, запрос будет выполнен без ошибок.If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL ServerSQL Server неявно уточнит имя столбца во вложенном запросе с помощью имени таблицы внешнего запроса.implicitly qualifies the column in the subquery with the table name in the outer query.

Множественные уровни вложенностиMultiple levels of nesting

Каждый вложенный запрос, в свою очередь, может содержать один или более вложенных запросов.A subquery can itself include one or more subqueries. В инструкцию можно вложить любое количество вложенных запросов.Any number of subqueries can be nested in a statement.

Следующий запрос осуществляет поиск сотрудников, занимающих должность менеджера по продажам.The following query finds the names of employees who are also sales persons.

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

Ниже приводится результирующий набор.Here is the result set.

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)

Самый глубоко вложенный запрос возвращает идентификаторы указанных сотрудников.The innermost query returns the sales person IDs. Запрос уровнем выше оперирует с полученными идентификаторами и возвращает контактные идентификаторы сотрудников.The query at the next higher level is evaluated with these sales person IDs and returns the contact ID numbers of the employees. Наконец, во внешнем запросе по полученным контактным идентификаторам извлекаются имена сотрудников.Finally, the outer query uses the contact IDs to find the names of the employees.

Этот запрос также можно выразить с помощью соединения:You can also express this query as a join:

USE AdventureWorks2016;
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

Связанные вложенные запросыCorrelated subqueries

Результат для нескольких запросов может быть получен путем выполнения одного вложенного запроса и подстановки полученного результата или результатов в предложение WHERE внешнего запроса.Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. В запросах, содержащих коррелированные вложенные запросы (также называемые повторяющимися вложенными запросами), вложенный запрос зависит по значению от внешнего запроса.In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. Это означает, что выполнение вложенного запроса повторяется по одному разу для каждой строки, которая может быть выбрана внешним запросом.This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. Такой запрос получает по одной записи для имени и фамилии каждого сотрудника, который в таблице SalesPerson имеет сумму премиальных, равную 5000, с соответствующими идентификаторами сотрудников в таблицах Employee и SalesPerson.This query retrieves one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables.

USE AdventureWorks2016;
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

Ниже приводится результирующий набор.Here is the result set.

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

(2 row(s) affected)

Предыдущий вложенный запрос данной инструкции не может быть выполнен независимо от внешнего запроса.The previous subquery in this statement cannot be evaluated independently of the outer query. В нем необходимо указать значение Employee.BusinessEntityID, однако это значение изменяется каждый раз, когда SQL ServerSQL Server проверяет различные строки в таблице Employee.It needs a value for Employee.BusinessEntityID, but this value changes as SQL ServerSQL Server examines different rows in Employee.
Результат запроса определяется следующим образом: SQL ServerSQL Server рассматривает каждую строку таблицы Employee на предмет включения в результаты, подставляя значение в каждой из строк во вложенный запрос.That is exactly how this query is evaluated: SQL ServerSQL Server considers each row of the Employee table for inclusion in the results by substituting the value in each row into the inner query. Например, если SQL ServerSQL Server сначала выполняет проверку строки для сотрудника Syed Abbas, переменная Employee.BusinessEntityID принимает значение 285, которое SQL ServerSQL Server и подставляет во вложенный запрос.For example, if SQL ServerSQL Server first examines the row for Syed Abbas, the variable Employee.BusinessEntityID takes the value 285, which SQL ServerSQL Server substitutes into the inner query.

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

Результатом является 0 (Syed Abbas не получал премиальных, потому что не является менеджером по продажам), поэтому выполнение внешнего запроса приводит к следующему результату:The result is 0 (Syed Abbas did not receive a bonus because he is not a sales person), so the outer query evaluates to:

USE AdventureWorks2016;
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

Поскольку условие не выполнено, строка для сотрудника Syed Abbas не включается в результат.Because this is false, the row for Syed Abbas is not included in the results. То же самое действие выполняется со строкой для сотрудника Pamela Ansman-Wolfe.Go through the same procedure with the row for Pamela Ansman-Wolfe. Из примера видно, что данная строка будет включена в результат.You will see that this row is included in the results.

Коррелированные вложенные запросы могут также включать в предложение FROM функции с табличным значением, указывая для них в качестве аргументов столбцы таблиц из внешнего запроса.Correlated subqueries can also include table-valued functions in the FROM clause by referencing columns from a table in the outer query as an argument of the table-valued function. В этом случае для каждой строки внешнего запроса выполняется функция с табличным значением, как и в случае с вложенным запросом.In this case, for each row of the outer query, the table-valued function is evaluated according to the subquery.

Типы вложенных запросовSubquery types

Вложенные запросы могут быть указаны во многих местах:Subqueries can be specified in many places:

Вложенные запросы с псевдонимамиSubqueries with Aliases

Многие инструкции, где вложенный и внешний запросы ссылаются на одну и ту же таблицу, могут быть переформулированы как самосоединения (соединения таблицы с самой собой).Many statements in which the subquery and the outer query refer to the same table can be stated as self-joins (joining a table to itself). Например, можно найти адреса сотрудников из конкретного региона с помощью вложенного запроса:For example, you can find addresses of employees from a particular state using a subquery:

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

Ниже приводится результирующий набор.Here is the result set.

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

(4 row(s) affected)

Можно также использовать самосоединение:Or you can use a self-join:

USE AdventureWorks2016;
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

Псевдонимы таблиц здесь необходимы, так как соединенная сама с собой таблица выступает в двух ролях.Table aliases are required because the table being joined to itself appears in two different roles. Псевдонимы можно также использовать во вложенных запросах, где и внешний, и внутренний запросы ссылаются на одну и ту же таблицу.Aliases can also be used in nested queries that refer to the same table in an inner and outer query.

USE AdventureWorks2016;
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

При использовании явных псевдонимов понятно, что ссылка на Person.Address во вложенном запросе означает не то же, что и ссылка во внешнем запросе.Explicit aliases make it clear that a reference to Person.Address in the subquery does not mean the same thing as the reference in the outer query.

Вложенные запросы с ключевым словом INSubqueries with IN

Результат вложенного запроса, в котором присутствует ключевое слово IN (или NOT IN) — это список из нуля или более значений.The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more values. После того как вложенный запрос вернул результат, он используется внешним запросом.After the subquery returns results, the outer query makes use of them.
Следующий запрос ищет названия всех колес, произведенных компанией Adventure Works Cycles.The following query finds the names of all the wheel products that Adventure Works Cycles makes.

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

Ниже приводится результирующий набор.Here is the result set.

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)

Эта инструкция выполняется в два шага.This statement is evaluated in two steps. Сначала внутренний запрос возвращает номер идентификатора подкатегории по соответствию названию «Wheel» (17).First, the inner query returns the subcategory identification number that matches the name 'Wheel' (17). Затем это значение подставляется во внешний запрос, который находит все названия изделий, имеющих соответствующие идентификаторы подкатегорий в столбце "Product".Second, this value is substituted into the outer query, which finds the product names that go with the subcategory identification numbers in Product.

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

Единственная разница в использовании соединения и вложенного запроса для этой и аналогичных задач заключается в том, что объединение позволяет включить в результат столбцы, содержащиеся в нескольких таблицах.One difference in using a join rather than a subquery for this and similar problems is that the join lets you show columns from more than one table in the result. Например: если нужно включить в результат название подкатегории, следует пользоваться соединением:For example, if you want to include the name of the product subcategory in the result, you must use a join version.

USE AdventureWorks2016;
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

Ниже приводится результирующий набор.Here is the result set.

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)

Следующий запрос ищет названия всех поставщиков, имеющих высокий кредитный рейтинг, у которых компания Adventure Works Cycles заказала как минимум 20 позиций, и средний срок поставки у которых не превышает 16 дней.The following query finds the name of all vendors whose credit rating is good, from whom Adventure Works Cycles orders at least 20 items, and whose average lead time to deliver is less than 16 days.

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

Ниже приводится результирующий набор.Here is the result set.

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)

Выполняется внутренний запрос и возвращаются номера идентификаторов поставщиков, которые соответствуют определениям вложенного запроса.The inner query is evaluated, producing the ID numbers of the vendors who meet the subquery qualifications. Затем выполняется внешний запрос.The outer query is then evaluated. Обратите внимание, что в предложение WHERE как внутреннего, так и внешнего запроса может быть включено несколько условий.Notice that you can include more than one condition in the WHERE clause of both the inner and the outer query.

При использовании соединения тот же запрос будет выражен так:Using a join, the same query is expressed like this:

USE AdventureWorks2016;
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

Соединение всегда может быть выражено в виде вложенного запроса.A join can always be expressed as a subquery. Вложенный запрос часто, но не всегда может быть выражен в виде соединения.A subquery can often, but not always, be expressed as a join. Это происходит потому, что соединения симметричны: можно соединить таблицы A и B в любом порядке и получить одинаковый результат.This is because joins are symmetric: you can join table A to B in either order and get the same answer. Для вложенных запросов это не всегда справедливо.The same is not true if a subquery is involved.

Вложенные запросы с ключевым словом NOT INSubqueries with NOT IN

Вложенные запросы с ключевым словом NOT IN также возвращают список из нуля или более значений.Subqueries introduced with the keyword NOT IN also return a list of zero or more values.
В следующем запросе выполняется поиск названий продуктов, не являющихся готовыми велосипедами.The following query finds the names of the products that are not finished bicycles.

USE AdventureWorks2016;
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

Эту инструкцию нельзя преобразовать в соединение.This statement cannot be converted to a join. Аналогичное соединение по неравенству имеет другой смысл: оно находит названия продуктов, которые принадлежат какой-либо подкатегории, отличной от готового велосипеда.The analogous not-equal join has a different meaning: It finds the names of products that are in some subcategory that is not a finished bicycle.

Вложенные запросы в инструкциях UPDATE, DELETE и INSERTSubqueries in UPDATE, DELETE, and INSERT Statements

Вложенные запросы могут использоваться в инструкциях UPDATE, DELETE, INSERT и SELECT языка обработки данных DML.Subqueries can be nested in the UPDATE, DELETE, INSERT and SELECT data manipulation (DML) statements.

В следующем примере удваивается значение столбца ListPrice таблицы Production.Product.The following example doubles the value in the ListPrice column in the Production.Product table. Вложенный запрос в предложении WHERE ссылается на таблицу Purchasing.ProductVendor для ограничения количества обновляемых строк таблицы Product только теми, у которых идентификатор BusinessEntity равен 1540.The subquery in the WHERE clause references the Purchasing.ProductVendor table to restrict the rows updated in the Product table to just those supplied by BusinessEntity 1540.

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

Ниже приведена эквивалентная инструкция UPDATE, использующая соединение:Here is an equivalent UPDATE statement using a join:

USE AdventureWorks2016;
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   

Вложенные запросы с операторами сравненияSubqueries with Comparison Operators

Вложенные запросы могут начинаться с одного из операторов сравнения (=, < >, >, > =, <, !Subqueries can be introduced with one of the comparison operators (=, < >, >, > =, <, ! >, !>, ! <, or < =).<, or < =).

Вложенный запрос, указанный с помощью неизмененного оператора сравнения (оператора сравнения, за которым не следуют ключевые слова ANY или ALL), должен возвратить одиночное значение, а не список значений как вложенные запросы, указанные с помощью IN.A subquery introduced with an unmodified comparison operator (a comparison operator not followed by ANY or ALL) must return a single value rather than a list of values, like subqueries introduced with IN. Если такой вложенный запрос возвращает более одного значения, SQL Server отображает сообщение об ошибке.If such a subquery returns more than one value, SQL Server displays an error message.

Чтобы использовать подзапрос, начинающийся с немодифицированного оператора сравнения, необходимо достаточно хорошо знать свои данные и природу проблемы, чтобы быть уверенным, что вложенный запрос возвратит точно одно значение.To use a subquery introduced with an unmodified comparison operator, you must be familiar enough with your data and with the nature of the problem to know that the subquery will return exactly one value.

Например, если предполагается, что каждый менеджер по продажам отвечает только за одну территорию продаж, и нужно найти клиентов, расположенных на территории, за которую отвечает Linda Mitchell, можно написать инструкцию с вложенным запросом, начинающимся с простого оператора сравнения =.For example, if you assume each sales person only covers one sales territory, and you want to find the customers located in the territory covered by Linda Mitchell, you can write a statement with a subquery introduced with the simple = comparison operator.

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

Однако если сотрудник Linda Mitchell работал более чем с одной территорией продаж, вы получите сообщение об ошибке.If, however, Linda Mitchell covered more than one sales territory, then an error message would result. Вместо оператора сравнения = может использоваться формулировка IN (также может использоваться = ANY).Instead of the = comparison operator, an IN formulation could be used (= ANY also works).

Вложенные запросы, начинающиеся с немодифицированных операторов сравнения, часто включают агрегатные функции, потому что они возвращают одиночное значение.Subqueries introduced with unmodified comparison operators often include aggregate functions, because these return a single value. Например, следующая инструкция находит названия всех продуктов, у которых цена по прейскуранту больше, чем средняя цена по прейскуранту.For example, the following statement finds the names of all products whose list price is greater than the average list price.

USE AdventureWorks2016;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product);
GO

Поскольку вложенные запросы, начинающиеся с неизмененных операторов сравнения, должны возвращать одиночное значение, они не могут включать предложения GROUP BY или HAVING (за исключением случаев, когда достоверно известно, что предложение GROUP BY или HAVING возвратит одиночное значение).Because subqueries introduced with unmodified comparison operators must return a single value, they cannot include GROUP BY or HAVING clauses unless you know the GROUP BY or HAVING clause itself returns a single value. Например, следующий запрос находит продукты, оцененные выше, чем самый дешевый продукт, который находится в подкатегории 14.For example, the following query finds the products priced higher than the lowest-priced product that is in subcategory 14.

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

Операторы сравнения с модификаторами ANY, SOME или ALLComparison Operators Modified by ANY, SOME, or ALL

Операторы сравнения с вложенными запросами могут быть уточнены с помощью ключевых слов ALL или ANY.Comparison operators that introduce a subquery can be modified by the keywords ALL or ANY. Модификатор SOME является эквивалентом модификатора ANY в стандарте ISO.SOME is an ISO standard equivalent for ANY.

Вложенные запросы с измененными операторами сравнения возвращают список из нуля или более значений и могут включать предложения GROUP BY или HAVING.Subqueries introduced with a modified comparison operator return a list of zero or more values and can include a GROUP BY or HAVING clause. Эти вложенные запросы могут быть переформулированы с использованием ключевого слова EXISTS.These subqueries can be restated with EXISTS.

Рассмотрим, например оператор сравнения >: >ALL будет означать "больше любого значения".Using the > comparison operator as an example, >ALL means greater than every value. Другими словами, это сравнение с максимальным значением.In other words, it means greater than the maximum value. Например, >ALL (1, 2, 3) означает "больше 3".For example, >ALL (1, 2, 3) means greater than 3. >ANY означает "больше по крайней мере одного значения", т. е. "больше минимума".>ANY means greater than at least one value, that is, greater than the minimum. Поэтому >ANY (1, 2, 3) означает "больше 1".So >ANY (1, 2, 3) means greater than 1. Чтобы строка результата вложенного запроса с >ALL удовлетворяла условию, заданному внешним запросом, значение в столбце, для которого вводится вложенный запрос, должно быть больше каждого значения из списка, возвращаемого вложенным запросом.For a row in a subquery with >ALL to satisfy the condition specified in the outer query, the value in the column introducing the subquery must be greater than each value in the list of values returned by the subquery.

Аналогичным образом, чтобы строка результата вложенного запроса с >ANY удовлетворяла условию, заданному внешним запросом, значение в столбце, для которого вводится вложенный запрос, должно быть больше хотя бы одного значения из списка, возвращаемого вложенным запросом.Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.

Следующий запрос сдержит пример вложенного запроса, используемого оператором сравнения с модификатором ANY.The following query provides an example of a subquery introduced with a comparison operator modified by ANY. Он вернет все продукты, цены на которые больше или равны максимальной цене в любой подкатегории продуктов.It finds the products whose list prices are greater than or equal to the maximum list price of any product subcategory.

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

Для каждой подкатегории продуктов внутренний запрос найдет максимальную цену.For each Product subcategory, the inner query finds the maximum list price. Внешний запрос получит эти значения и определит, какие цены отдельных продуктов больше или равны максимальной цене в любой из подкатегорий продуктов.The outer query looks at all of these values and determines which individual product's list prices are greater than or equal to any product subcategory's maximum list price. Если ключевое слово ANY заменить на ALL, запрос вернет только те продукты, цена которых больше или равна всем ценам, возвращаемым внутренним запросом.If ANY is changed to ALL, the query will return only those products whose list price is greater than or equal to all the list prices returned in the inner query.

Если вложенный запрос не возвращает значений, весь запрос не возвратит никаких значений.If the subquery does not return any values, the entire query fails to return any values.

Оператор =ANY эквивалентен оператору IN.The =ANY operator is equivalent to IN. Например, чтобы найти названия всех колес, производимых компанией Adventure Works Cycle, можно использовать IN или =ANY.For example, to find the names of all the wheel products that Adventure Works Cycles makes, you can use either IN or =ANY.

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

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

Вот результирующий набор, возвращаемый любым из этих запросов:Here is the result set for either query:

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)

Однако оператор <>ANY отличается от NOT IN: <>ANY означает "не равно a или не равно b или не равно c".The <>ANY operator, however, differs from NOT IN: <>ANY means not = a, or not = b, or not = c. NOT IN означает "не равно a и не равно b и не равно c".NOT IN means not = a, and not = b, and not = c. <>ALL означает то же, что и NOT IN.<>ALL means the same as NOT IN.

Например, следующий запрос отобразит заказчиков, находящихся на территории, где не работает ни один менеджер по продажам.For example, the following query finds customers located in a territory not covered by any sales persons.

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

В результат включены все заказчики, кроме тех, чьим территориям продаж соответствует NULL, так как любая территория, назначенная заказчику, обслуживается менеджером по продажам.The results include all customers, except those whose sales territories are NULL, because every territory that is assigned to a customer is covered by a sales person. Внутренний запрос находит все территории продаж, обслуживаемые менеджерами по продажам, а затем для каждой территории внешний запрос находит заказчиков, которые ей не принадлежат.The inner query finds all the sales territories covered by sales persons, and then, for each territory, the outer query finds the customers who are not in one.

По этой же причине, если использовать NOT IN в этом запросе, в результат не войдет ни один из заказчиков.For the same reason, when you use NOT IN in this query, the results include none of the customers.

Те же результаты можно получить с помощью оператора <>ALL, который эквивалентен NOT IN.You can get the same results with the <>ALL operator, which is equivalent to NOT IN.

Вложенные запросы с ключевым словом EXISTSSubqueries with EXISTS

Если вложенный запрос указывается с помощью ключевого слова EXISTS, он выступает в роли проверки на существование.When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test. В предложении WHERE внешнего запроса проверяется факт существования строк, возвращенных вложенным запросом.The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. Вложенный запрос не выдает никаких данных, а возвращает значение TRUE или FALSE.The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

Вложенный запрос, созданный с помощью ключевого слова EXISTS, имеет следующий синтаксис:A subquery introduced with EXISTS has the following syntax:

WHERE [NOT] EXISTS (subquery)

Следующий запрос ищет названия всех продуктов, которые находятся в подкатегории Wheels:The following query finds the names of all products that are in the Wheels subcategory:

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

Ниже приводится результирующий набор.Here is the result set.

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)

Чтобы понять результаты данного запроса, необходимо рассмотреть наименование каждого продукта.To understand the results of this query, consider the name of each product in turn. Это позволит узнать, возвращается ли в результате выполнения данного вложенного запроса хотя бы одна строка.Does this value cause the subquery to return at least one row? Иными словами, выдается ли в результате проверки на существование значение TRUE.In other words, does the query cause the existence test to evaluate to TRUE?

Обратите внимание на то, что вложенные запросы, введенные с помощью ключевого слова EXISTS, отличаются от других вложенных запросов следующим образом.Notice that subqueries that are introduced with EXISTS are a bit different from other subqueries in the following ways:

  • Перед ключевым словом EXISTS не ставится имя столбца, константы или другого выражения.The keyword EXISTS is not preceded by a column name, constant, or other expression.
  • Список выборки для вложенного запроса, указанного с помощью ключевого слова EXISTS, практически всегда состоит из знака "звездочка" (*).The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). Так как производится проверка только на предмет существования строк, удовлетворяющих заданным во вложенном запросе условиям, то нет необходимости выводить имена найденных столбцов.There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

Важность ключевого слова EXISTS обусловлена тем, что часто без него невозможно найти иное решение без вложенных запросов.The EXISTS keyword is important because frequently there is no alternative formulation without subqueries. Хотя некоторые запросы, созданные с помощью ключевого слова EXISTS, не могут быть выражены по-другому, многие запросы для достижения подобных результатов используют IN или оператор сравнения, измененный с помощью ANY или ALL.Although some queries that are created with EXISTS cannot be expressed any other way, many queries can use IN or a comparison operator modified by ANY or ALL to achieve similar results.

Например, предыдущий запрос может быть задан с использованием оператора IN:For example, the preceding query can be expressed by using IN:

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

Вложенные запросы с оператором NOT EXISTSSubqueries with NOT EXISTS

Оператор NOT EXISTS работает так же, как и оператор EXISTS, за исключением того, что предложение WHERE, в котором используется этот оператор, выполняется, если вложенный запрос не возвращает ни одной строки.NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.

Например чтобы найти имена продуктов, не находящихся в подкатегории wheels:For example, to find the names of products that are not in the wheels subcategory:

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

Вложенные запросы, используемые вместо выраженияSubqueries Used in place of an Expression

В языке Transact-SQLTransact-SQL вложенный запрос может быть заменен в любом месте, где выражение может использоваться в инструкциях SELECT, UPDATE, INSERT и DELETE, за исключением списка ORDER BY.In Transact-SQLTransact-SQL, a subquery can be substituted anywhere an expression can be used in SELECT, UPDATE, INSERT, and DELETE statements, except in an ORDER BY list.

Следующий пример показывает, как можно использовать это улучшение.The following example illustrates how you might use this enhancement. Запрос находит цены на все горные велосипеды, их среднюю цену и разницу между средней ценой и ценой каждого горного велосипеда.This query finds the prices of all mountain bike products, their average price, and the difference between the price of each mountain bike and the average price.

USE AdventureWorks2016;
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

См. также:See Also

IN (Transact-SQL) IN (Transact-SQL)
EXISTS (Transact-SQL) EXISTS (Transact-SQL)
ALL (Transact-SQL) ALL (Transact-SQL)
SOME | ANY (Transact-SQL) SOME | ANY (Transact-SQL)
Соединения Joins
Операторы сравнения (Transact-SQL)Comparison Operators (Transact-SQL)