サブクエリ (SQL Server)Subqueries (SQL Server)

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

サブクエリとは、SELECTINSERTUPDATE、または 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. 次の例では、SELECT ステートメント内にある MaxUnitPrice という列に対する式としてサブクエリを使用します。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

1 つ上のレベルの SELECT ステートメントの中で入れ子になったサブクエリの SELECT の構成要素は、次のようになります。A subquery nested in the outer SELECT statement has the following components:

  • 標準の選択リスト構成要素を含んでいる標準の SELECT クエリ。A regular SELECT query including the regular select list components.
  • 1 つ以上のテーブル名またはビュー名を含んでいる標準の 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 句を含むことはできず、TOP 句も指定された場合に ORDER BY 句を含むことだけができます。It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

サブクエリは、1 つ上のレベルの SELECTINSERTUPDATE、または DELETE の各ステートメントの WHERE 句または HAVING 句の中、あるいは別のサブクエリの中で入れ子にできます。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.

あるテーブルがサブクエリにのみ現れていて、1 つ上のレベルのクエリに現れていない場合、そのテーブルの列は出力 (1 つ上のレベルのクエリの選択リスト) に含めることはできません。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 式 comparison_operator [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. 概念的には、サブクエリの結果が 1 つ上のレベルのクエリに代入されることになります。ただし、サブクエリを含む Transact-SQLTransact-SQL ステートメントが、SQL ServerSQL Server によってこのように処理されるとは限りません。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).

サブクエリの種類は、大きく 3 つに分けられます。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:

  • 比較演算子で導かれたサブクエリの選択リストには、式または列名を 1 つしか入れることができません。ただし、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).
  • 1 つ上のレベルのクエリの 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.
  • ntexttextimage の各データ型は、サブクエリの選択リストでは使用できません。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.
  • GROUP BY を含むサブクエリでは、DISTINCT キーワードは使用できません。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

次の例で、外側のクエリの WHERE 句内の BusinessEntityID 列は、外側のクエリの 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 により、外側のクエリのテーブル名で暗黙的に修飾されます。SQL ServerSQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

複数レベルの入れ子Multiple levels of nesting

サブクエリには 1 つ以上のサブクエリを含めることができます。A subquery can itself include one or more subqueries. 1 つのステートメント内で任意の数のサブクエリを入れ子にできます。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)

最も内側のクエリは、販売員 ID を返します。The innermost query returns the sales person IDs. 1 つ上のレベルのクエリはその販売員 ID で評価され、対応する従業員の連絡先 ID 番号を返します。The query at the next higher level is evaluated with these sales person IDs and returns the contact ID numbers of the employees. 最後に、外側のクエリはこの連絡先 ID を使用して、従業員の名前を検索します。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

多くのクエリは、サブクエリを 1 回実行し、その結果である 1 つまたは複数の値を外側のクエリの 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. つまり、外側のクエリで選択される各行に対して 1 回ずつ、サブクエリが繰り返し実行されることになります。This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. 次のクエリでは、各従業員の姓と名を検索し、その中から SalesPerson テーブルで示される特別手当の値が 5,000 で、従業員 ID が 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 の行が調べられると、SQL ServerSQL Server により内側のクエリに代入された値 285 が変数 Employee.BusinessEntityID で取得されます。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

サブクエリと 1 つ上のレベルのクエリで同じテーブルを参照しているステートメントは、テーブルをそのテーブル自体に結合する自己結合として表すこともできます。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

テーブルをそれ自体に結合する場合、テーブルが 2 つの異なる役割で使用されるので、テーブルの別名が必要になります。Table aliases are required because the table being joined to itself appears in two different roles. 別名は、次のように、内側のクエリと 1 つ上のレベルのクエリで同じテーブルを参照する入れ子になったクエリでも使えます。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 への参照が、1 つ上のレベルのクエリ内の参照と同一ではないことが明確になります。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.

IN で導かれるサブクエリSubqueries with IN

IN または NOT IN で導かれたサブクエリの結果は、0 個以上の値のリストになります。The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more values. サブクエリが結果を返すと、1 つ上のレベルのクエリがこの結果を使用します。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)

このステートメントは、2 段階で評価されます。This statement is evaluated in two steps. まず、内側のクエリが名前 "Wheel" と一致するサブカテゴリの ID 番号 (17) を返します。First, the inner query returns the subcategory identification number that matches the name 'Wheel' (17). 次に、この値が 1 つ上のレベルのクエリに代入され、1 つ上のレベルのクエリがサブカテゴリの ID 番号に対応する製品名を 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

この例やこれに似た問題に対して、サブクエリではなく結合を使用すると、1 つの違いが生じます。結合では、複数のテーブルの列が結果に表示されます。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)

まず、内側のクエリが評価され、サブクエリの条件を満たすベンダーの ID 番号が返されます。The inner query is evaluated, producing the ID numbers of the vendors who meet the subquery qualifications. 次に、1 つ上のレベルのクエリが評価されます。The outer query is then evaluated. 内側のクエリと 1 つ上のレベルのクエリ両方の 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 IN で導かれるサブクエリSubqueries with NOT IN

キーワード NOT IN で導かれるサブクエリも、0 個以上の値のリストを返します。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、および INSERT ステートメントでのサブクエリSubqueries in UPDATE, DELETE, and INSERT Statements

サブクエリは、UPDATEDELETEINSERTSELECT の各データ操作言語 (DML) ステートメントで入れ子にできます。Subqueries can be nested in the UPDATE, DELETE, INSERT and SELECT data manipulation (DML) statements.

次の例では、ListPrice テーブルの Production.Product 列の値が 2 倍になります。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 < =).

修飾されていない比較演算子 (後ろに ANYALL がない比較演算子) で導かれるサブクエリでは、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.

修飾されていない比較演算子で導かれるサブクエリを使用するには、データや問題の性質を十分に理解して、そのサブクエリで値が 1 つしか返されないことを把握しておく必要があります。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.

たとえば、販売員 1 人が 1 販売区域を担当すると仮定した場合に、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、または ALL で修飾された比較演算子Comparison 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.

修飾した比較演算子で導かれたサブクエリは、0 個以上の値のリストを返し、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 は少なくとも 1 つの値より大きい、つまり最小値より大きいという意味です。>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 は、行が外側のクエリで指定された条件を満たすには、サブクエリを導く列の値がサブクエリによって返される値のリストの少なくとも 1 つの値よりも大きい必要があることを意味します。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

Product サブカテゴリごとに、内側のクエリが最も高い定価を検索します。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. ANYALL に変更すると、クエリは内側のクエリで返されたすべての定価以上の定価が付けられた製品のみを返します。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 Cycles が製造しているすべてのホイール製品の名前を検索するときに、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 は、"not = a または not = b または not = c" という意味です。The <>ANY operator, however, differs from NOT IN: <>ANY means not = a, or not = b, or not = c. NOT IN は、"not = a かつ not = b かつ not = c" という意味です。NOT IN means not = a, and not = b, and not = c. <>ALLNOT 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.

NOT IN と同じ意味の <>ALL 演算子を使用しても同じ結果が得られます。You can get the same results with the <>ALL operator, which is equivalent to NOT IN.

EXISTS を使ったサブクエリSubqueries 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. その値があるとき、サブクエリから 1 行以上が返されるでしょうか。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 EXISTS を使用したサブクエリSubqueries with NOT EXISTS

NOT EXISTS の機能は EXISTS と似ています。ただし、NOT 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 では、SELECTUPDATEINSERTDELETE の各ステートメントで式が使えるところであればどこでも、サブクエリを式の代わりに使用できます。ただし、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)