次の方法で共有

サブクエリ (SQL Server)

  • [アーティクル]

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

サブクエリとは、SELECT、INSERT、UPDATE、または DELETE の各ステートメントの内部、または別のサブクエリの内部で入れ子になっているクエリです。

この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

サブクエリは、式が使えるところであればどこにでも使用できます。 この例では、SELECT ステートメントで MaxUnitPrice という名前の列式として、サブクエリが使用されています。

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

サブクエリの基礎

サブクエリは内部クエリや内部選択と呼ばれることもあります。また、サブクエリを含むステートメントは外部クエリや外部選択と呼ばれます。

サブクエリを含む Transact-SQL ステートメントの多くは、サブクエリを使う代わりに結合を使って表現することもできます。 サブクエリでしか発生しない問題もあります。 通常、Transact-SQL では、サブクエリを含むステートメントと、意味は同じでもサブクエリを含まないステートメントとの間にパフォーマンスの違いはありません。 SQL Server がクエリを処理する方法のアーキテクチャについては、SQL ステートメントの処理を参照してください。 ただし、存在を検査する必要がある場合には、結合によってパフォーマンスが向上することもあります。 それ以外の場合は、入れ子になったクエリを外側のクエリの結果ごとに処理し、重複が確実に解消されるようにする必要があります。 このような場合、結合のアプローチを使った方が良い結果になります。

次の例で示すサブクエリ SELECT と結合 SELECT からは、同じ結果セットと実行プランが返されます。

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

1 つ上のレベルの SELECT ステートメントの中で入れ子になったサブクエリの SELECT の構成要素は、次のようになります。

  • 標準の選択リスト構成要素を含んでいる標準の SELECT クエリ。
  • 1 つ以上のテーブル名またはビュー名を含んでいる標準の FROM 句。
  • 省略可能な WHERE 句。
  • 省略可能な GROUP BY 句。
  • 省略可能な HAVING 句。

サブクエリの SELECT クエリは常にかっこで囲みます。 COMPUTE 句または FOR BROWSE 句を含むことはできず、TOP 句も指定された場合に ORDER BY 句を含むことだけができます。

サブクエリは、1 つ上のレベルの SELECT、INSERT、UPDATE、または DELETE の各ステートメントの WHERE 句または HAVING 句の中、あるいは別のサブクエリの中で入れ子にできます。 32 レベルまで入れ子にできますが、上限はクエリの複雑さと使用可能なメモリによって変わります。 個々のクエリは、32 レベルまで入れ子にするのに対応していません。 サブクエリは、単一の値を返す限り、式が使えるところであればどこにでも使用できます。

あるテーブルがサブクエリにのみ現れていて、外側のクエリに現れていない場合、そのテーブルの列は出力 (外側のクエリの選択リスト) に含めることはできません。

サブクエリを含むステートメントは、通常、次の形式のいずれかになります。

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

Transact-SQL ステートメントの中には、サブクエリが独立したクエリであるように評価されるものがあります。 概念的には、サブクエリの結果が 外側のクエリに代入されることになります。ただし、サブクエリを含む Transact-SQL ステートメントが、SQL Server によってこのように処理されるとは限りません。

サブクエリの種類は、大きく 3 つに分けられます。 具体的には、次のように大別されます。

  • IN で導かれるリスト、あるいは ANY または ALL で修飾された比較演算子で導かれるリストを操作するサブクエリ。
  • 修飾されていない比較演算子で導かれ、単一の値を返す必要があるサブクエリ
  • EXISTS で導かれる、存在を検査するサブクエリ。

サブクエリのルール

サブクエリには次の制限があります。

  • 比較演算子で導かれたサブクエリの選択リストには、式または列名を 1 つしか入れることができません。ただし、EXISTS および IN では、それぞれ SELECT * とリストを使用できます。
  • 1 つ上のレベルのクエリの WHERE 句に列名が含まれている場合、サブクエリの選択リストで指定されている列との結合互換性が必要です。
  • ntext、text、image の各データ型は、サブクエリの選択リストでは使用できません。
  • 単一の値を返す必要があるため、修飾されていない比較演算子 (キーワード ANY または ALL が後に付いていないもの) によって導入されるサブクエリには、GROUP BY 句および HAVING 句を含めることはできません。
  • DISTINCT を含むサブクエリでは、 GROUP BY キーワードは使用できません。
  • COMPUTE 句と INTO 句は指定できません。
  • ORDER BY 句を指定できるのは、TOP 句が指定されているときだけです。
  • サブクエリで作成されたビューは、更新できません。
  • EXISTS で導かれたサブクエリの選択リストには、通例、単一の列名ではなくアスタリスク (*) が使用されます。 EXISTS で導かれるサブクエリの規則は、標準の選択リストの規則と同じです。これは、EXISTS で導かれるサブクエリは存在検査を行うもので、データではなく TRUE または FALSE を返すためです。

サブクエリで使用する列名を見込みありと評価する

次の例で、外側のクエリの WHERE 句内の BusinessEntityID 列は、外側のクエリの FROM 句内のテーブル名 (Sales.Store) で暗黙的に修飾されています。 サブクエリの選択リスト内の CustomerID への参照は、サブクエリの FROM 句、つまり Sales.Customer テーブルで修飾されています。

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

一般的な規則としては、ステートメント内の列名は、同じレベルの FROM 句で参照しているテーブルで暗黙的に修飾されます。 サブクエリの FROM 句で参照しているテーブルに列名が存在しない場合、外側のクエリの FROM 句で参照しているテーブルで暗黙的に見込みありと評価されます。

これらの暗黙的な前提データを明示的に指定すると、クエリは次のようになります。

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

テーブル名を明示的に記述しても間違いであるということはありません。明示的に修飾することで、テーブル名に関する暗黙的な前提データをいつでもオーバーライドすることができます。

重要

サブクエリで参照している列が、サブクエリの FROM 句で参照しているテーブルにない場合でも、外側のクエリの FROM 句で参照しているテーブルに存在すれば、エラーが発生することなくクエリが実行されます。 サブクエリで参照している列は、SQL Server により、外側のクエリのテーブル名で暗黙的に見込みありと評価されます。

複数レベルの入れ子

サブクエリには 1 つ以上のサブクエリを含めることができます。 1 つのステートメント内で任意の数のサブクエリを入れ子にできます。

次のクエリでは、従業員であり販売員でもある者の名前を検索しています。

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

結果セットは次のとおりです。

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 を返します。 1 つ上のレベルのクエリはその販売員 ID で評価され、対応する従業員の連絡先 ID 番号を返します。 最後に、外側のクエリはこの連絡先 ID を使用して、従業員の名前を検索します。

このクエリは、次のように結合の形で記述することもできます。

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

相関サブクエリ

多くのクエリは、サブクエリを 1 回実行し、その結果である 1 つまたは複数の値を外側のクエリの WHERE 句に代入することにより評価されます。 相関サブクエリ (繰り返しサブクエリとも呼びます) を含むクエリでは、サブクエリの値は外側のクエリによって決まります。 つまり、外側のクエリで選択される各行に対して 1 回ずつ、サブクエリが繰り返し実行されることになります。

このクエリは、SalesPerson テーブルの特別手当が 5000 で、従業員の ID 番号が Employee テーブルと SalesPerson テーブルで一致する各従業員の姓名のインスタンスを 1 つ取得します。

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

結果セットは次のとおりです。

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

(2 row(s) affected)

上記のステートメントのサブクエリは、外側のクエリから独立して評価できません。 これは、Employee.BusinessEntityID の値を必要としますが、SQL Server によって Employee のさまざまな行が調べられると、この値は変化します。 このクエリでは、SQL Server により、Employee テーブルの各行の値が内側のクエリに代入されることによって、各行を結果に含めるかどうかが判断されます。 たとえば、SQL Server により、最初に Syed Abbas の行が調べられると、SQL Server により内側のクエリに代入された値 285 が変数 Employee.BusinessEntityID で取得されます。 これら 2 つのクエリのサンプルは、相関サブクエリを使用した前のサンプルの分解を表します。

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

結果は 0.00 なので (Syed Abbas は営業担当者ではないので特別手当が出ません)、外側のクエリは次のように評価されます。

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

これは間違いであるため、Syed Abbas の行は、相関サブクエリを使用した前のサンプル クエリの結果には含まれません。 同じ手順を Pamela Ansman-Wolfe の行に対して実行します。 WHERE 5000 IN (5000) には結果が含まれているため、この行は結果に含まれることがわかります。

相関サブクエリでは、特定のテーブルに含まれている列をテーブル値関数の引数として外側のクエリで参照することにより、FROM 句にテーブル値関数を含めることもできます。 この場合、外側のクエリの各行について、サブクエリに従ってテーブル値関数が評価されます。

サブクエリの種類

サブクエリは、次のようにクエリのさまざまな部分で指定できます。

テーブルの別名を使用したサブクエリ

サブクエリと 1 つ上のレベルのクエリで同じテーブルを参照しているステートメントは、テーブルをそのテーブル自体に結合する自己結合として表すこともできます。 たとえば、サブクエリを使用して特定の州に住む従業員の住所を見つけられます。

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

結果セットは次のとおりです。

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

(4 row(s) affected)

または、次のように自己結合を使うこともできます。

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

テーブルをそれ自体に結合する場合、テーブルが 2 つの異なる役割で使用されるので、テーブルの別名 e1 と e2 が必要になります。 別名は、次のように、内側のクエリと 1 つ上のレベルのクエリで同じテーブルを参照する入れ子になったクエリでも使えます。

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

テーブルの別名を明示的に指定すると、サブクエリ内の Person.Address への参照が、外側のクエリ内のリファレンスと同一ではないことが明確になります。

IN を使用するサブクエリ

IN または NOT IN で導かれたサブクエリの結果は、0 個以上の値のリストになります。 サブクエリが結果を返すと、1 つ上のレベルのクエリがこの結果を使用します。 次のクエリでは、Adventure Works Cycles が製造しているすべてのホイール製品の名前が検索されます。

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

結果セットは次のとおりです。

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 段階で評価されます。 まず、内側のクエリが名前 Wheel と一致するサブカテゴリの ID 番号 (17) を返します。 次に、この値が 外側のクエリに代入され、外側のクエリがサブカテゴリの ID 番号に対応する製品名を Production.Product の中から検索します。

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

この例やこれに似た問題に対して、サブクエリではなく結合を使用すると、1 つの違いが生じます。結合では、複数のテーブルの列が結果に表示されます。 たとえば、製品のサブカテゴリ名を結果に含めるには、次のような結合を使う必要があります。

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

結果セットは次のとおりです。

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 日未満のベンダーの名前を検索します。

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

結果セットは次のとおりです。

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 番号が返されます。 次に、1 つ上のレベルのクエリが評価されます。 内側のクエリと 1 つ上のレベルのクエリ両方の WHERE 句に、複数の条件を含めることができます。

結合を使うと、上記のクエリは次のように表されます。

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

結合は常にサブクエリとして表すことができます。 サブクエリは、多くの場合、結合として表すことができますが、常に表せるわけではありません。 これは、結合に対照性があるためです。つまり、テーブル A とテーブル B をどのような順序で結合しても、得られる答えは同じになります。 サブクエリを使った場合、同じことが当てはまりません。

NOT IN を使用するサブクエリ

キーワード NOT IN を使用するサブクエリも、0 個以上の値のリストを返します。 次のクエリでは、自転車 (完成品) 以外の製品名が検索されます。

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

このステートメントは結合に変換できません。 非等価結合はこれとよく似ていますが、意味は異なります。つまり、自転車 (完成品) 以外のサブカテゴリに属する製品名が検索されます。

UPDATE、DELETE、および INSERT ステートメントでのサブクエリ

サブクエリは、UPDATE、DELETE、INSERT、SELECT の各データ操作言語 (DML) ステートメントで入れ子にできます。

次の例は、ListPrice テーブルの Production.Product 列の値を倍にします。 WHERE 句のサブクエリでは Purchasing.ProductVendor テーブルを参照して、Product テーブルで更新される行を BusinessEntity 1540 の行だけに制限しています。

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

上のクエリと同等の、結合を使用した UPDATE ステートメントを次に示します。

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

他のサブクエリで同じテーブル自体が参照されている場合に明確にするには、ターゲット テーブルの別名を使用します。

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

比較演算子によるサブクエリ

サブクエリは、比較演算子 (=、< >、>、> =、<、! >、! <、または < =) のいずれかで導入できます。

修飾されていない比較演算子 (後ろに ANY や ALL がない比較演算子) で導かれるサブクエリでは、IN によって導かれるサブクエリと同様に、値のリストでなく単一の値を返す必要があります。 このようなサブクエリから複数の値が返された場合は、SQL Server によりエラー メッセージが表示されます。

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

たとえば、販売員 1 人が 1 販売区域を担当すると仮定した場合に、Linda Mitchell が担当する区域の顧客を検索するには、次のように単純な = 比較演算子を使用したステートメントを作成できます。

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

ただし、Linda Mitchell が複数の販売区域を担当している場合は、エラー メッセージが返されます。 = 比較演算子の代わりに、IN 式を使用できます (=ANY も動作します)。

集計関数は単一の値を返すため、多くの場合、修飾されていない比較演算子で導かれるサブクエリには、集計関数が含まれます。 たとえば、次のステートメントでは、表示価格が平均表示価格よりも高いすべての製品の名前が検索されます。

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

修飾されていない比較演算子で導入されるサブクエリでは単一の値を返す必要があるため、GROUP BY 句や HAVING 句を含めることはできません。ただし、GROUP BY 句や HAVING 句が単一の値を返すことがわかっている場合は例外です。 たとえば、次のクエリでは、ProductSubcategoryID が 14 の製品で、最低価格の製品より高い価格の付けられた製品が検索されます。

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

ANY、SOME、または ALL で修飾された比較演算子

サブクエリを導入する比較演算子は、キーワード ALL または ANY で修飾できます。 SOME は ANY に相当する ISO 標準です。 これらの比較演算子の詳細については、「SOME | ANY」を参照してください。

修飾した比較演算子で導かれたサブクエリは、0 個以上の値のリストを返し、GROUP BY 句または HAVING 句を含むことができます。 これらのサブクエリは、EXISTS を使用して書き換えることができます。

> 比較演算子を例として使用すると、> ALL は、どの値よりも大きいという意味になります。 つまり、最大値よりも大きいという意味です。 たとえば、> ALL (1, 2, 3) は 3 より大きいという意味になります。 > ANY は少なくとも 1 つの値より大きい、つまり最小値より大きいという意味です。 したがって、> ANY (1, 2, 3) は 1 より大きいという意味になります。

> ALL が含まれたサブクエリの行が外側のクエリで指定された条件を満たすには、サブクエリを導く列の値がサブクエリによって返される値のリストのどの値よりも大きい必要があります。

同様に、> ANY は、行が外側のクエリで指定された条件を満たすには、サブクエリを導く列の値がサブクエリによって返される値のリストの少なくとも 1 つの値よりも大きい必要があることを意味します。

次のクエリでは、ANY によって修飾された比較演算子で導入されるサブクエリの例を示します。 このクエリでは、製品のサブカテゴリの中から最も高い定価以上の定価が付けられた製品を検索します。

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

Product サブカテゴリごとに、内側のクエリが最も高い定価を検索します。 外側のクエリは、これらすべての値を比較し、製品のサブカテゴリの中で最も高い定価以上の定価が付けられた製品を決定します。 ANY を ALL に変更すると、クエリは内側のクエリで返されたすべてのリスト価格以上の定価が付けられた製品のみを返します。

サブクエリが値を返さない場合は、クエリ全体が値を返しません。

= ANY 演算子は IN と同じ意味を持ちます。 たとえば、Adventure Works Cycles が製造しているすべてのホイール製品の名前を検索するときに、IN または = 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

次に各クエリの結果セットを示します。

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" を意味します
  • NOT IN は、"not = a かつ not = b かつ not = c" を意味します
  • <> ALL は NOT IN と同じ意味になります

たとえば、次のクエリでは、担当販売員がいない区域の顧客を検索します。

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

結果には、販売区域が NULL の顧客を除くすべての顧客が含まれます。これは、顧客に割り当てられている区域はすべて、いずれかの販売員が担当しているためです。 内側のクエリで販売員が担当しているすべての営業担当地域が検索されてから、外部クエリで各区域に属していない顧客が検索されます。

同様の理由で、このクエリで NOT IN を使用すると、結果にはどの顧客も含められません。

NOT IN と同じ意味の <> ALL 演算子を使用しても同じ結果が得られます。

EXISTS を使用するサブクエリ

サブクエリの導入にキーワード EXISTS を使用した場合、そのサブクエリは存在検査として機能します。 外側のクエリの WHERE 句により、このサブクエリから返される行が存在するかどうかがテストされます。 サブクエリでは実際にはデータは生成されず、TRUE または FALSE の値が返されます。

EXISTS を使用して導入するサブクエリの構文は、WHERE [NOT] EXISTS (subquery) となります。

次のクエリは、Wheels サブカテゴリに属するすべての製品の名前を検出します。

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

結果セットは次のとおりです。

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)

このクエリの結果を理解するには、各製品の名前を順番に考察してください。 その値があるとき、サブクエリから 1 行以上が返されるでしょうか。 つまり、存在検査は TRUE と評価されるでしょうか。

EXISTS を使用して導入するサブクエリは、他のサブクエリとは次の点で少し異なります:

  • キーワード EXISTS の前に、列名、定数、またはその他の式は配置されません。
  • ほとんどの場合、EXISTS で導かれたサブクエリの選択リストはアスタリスク (*) で構成されます。 サブクエリで指定された条件を満たす行が存在するかどうかを検査するだけなので、列名をリストする理由はありません。

サブクエリを使用しない記述形式で置き換えることのできない場合が多いので、EXISTS キーワードは重要です。 EXISTS を使って作成したクエリの中には他の表現に変えられないものがありますが、多くのクエリでは、IN を使用したり、ANY または ALL で修飾した比較演算子を使用したりすることにより、同じような結果を得ることができます。

たとえば、上記のクエリは IN を使用して表現できます。

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

NOT EXISTS を使用するサブクエリ

NOT EXISTS の機能は EXISTS と似ています。ただし、WHERE 句が条件を満たすのは、対応するサブクエリによって返される行がない場合です。

たとえば、ホイール 下位カテゴリに含まれていない製品の名前を検出するには、次のクエリを実行します。

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

式の代わりに使われるサブクエリ

Transact-SQLでは、SELECT、UPDATE、INSERT、DELETE、の各ステートメントで式が使えるところであればどこでも、サブクエリを式の代わりに使用できます。ただし、 ORDER BY リストにはサブクエリを使用できません。

次の例に、この拡張機能の使用方法を示します。 次のクエリにより、すべてのマウンテン バイク製品の価格、平均価格、および各マウンテン バイクの価格と平均価格との差がわかります。

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

関連するコンテンツ