サブクエリ (SQL Server)

適用対象: SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

Note

この記事のサンプルには、AdventureWorks サンプル データベースからダウンロードできる AdventureWorks2016 データベースを使用します。

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

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

サブクエリの基礎

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

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

次の例は、同じ結果セットと実行プランを返すサブクエリ SELECT と結合 SELECT の両方を示しています。

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 の構成要素は、次のようになります。

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

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

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

テーブルが外部クエリではなくサブクエリにのみ表示される場合、そのテーブルの列を出力 (外部クエリの選択リスト) に含めることはできません。

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

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

一部の Transact-SQL ステートメントでは、サブクエリは独立したクエリであるかのように評価できます。 概念的には、サブクエリの結果は外部クエリに置き換えます (ただし、これは必ずしもSQL Serverがサブクエリを使用して Transact-SQL ステートメントを処理する方法であるとは限りません)。

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

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

サブクエリの規則

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

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

サブクエリで列名を修飾する

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

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

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

これらの暗黙的な前提条件が指定されたクエリの外観を次に示します。

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

テーブル名を明示的に指定することは間違いではありません。また、テーブル名に関する暗黙的な前提を明示的な修飾でオーバーライドすることは常に可能です。

重要

サブクエリで参照している列が、サブクエリの FROM 句で参照しているテーブルにない場合でも、外側のクエリの FROM 句で参照しているテーブルに存在すれば、エラーが発生することなくクエリが実行されます。 SQL Serverは、サブクエリ内の列を外部クエリのテーブル名で暗黙的に修飾します。

複数レベルの入れ子

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

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

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

結果セットは次のようになります。

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 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

相関サブクエリ

多くのクエリは、サブクエリを 1 回実行し、その結果である 1 つまたは複数の値を外側のクエリの WHERE 句に代入することにより評価されます。 相関サブクエリ (繰り返しサブクエリとも呼びます) を含むクエリでは、サブクエリの値は外側のクエリによって決まります。 つまり、外側のクエリで選択される各行に対して 1 回ずつ、サブクエリが繰り返し実行されることになります。 次のクエリでは、各従業員の姓と名を検索し、その中から SalesPerson テーブルで示される特別手当の値が 5,000 で、従業員 ID が Employee テーブルと SalesPerson テーブルで一致しているものを取得しています。

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

結果セットは次のようになります。

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

(2 row(s) affected)

このステートメントの前のサブクエリは、外部クエリとは別に評価できません。 Employee.BusinessEntityID の値が必要ですが、Employee で異なる行SQL Server調べると、この値が変わります。 まさにこのクエリの評価方法です。SQL Serverでは、各行の値を内部クエリに置き換えることで、Employee テーブルの各行が結果に含まれると見なされます。 たとえば、SQL Server最初に の行Syed Abbasを調べる場合、変数 Employee.BusinessEntityID は値 285 を受け取り、SQL Server内部クエリに置き換えます。 これら 2 つのクエリのサンプルは、相関サブクエリを使用した前のサンプルの分解を表します。

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

結果は 0.00 (Syed Abbas 営業担当者ではないのでボーナスを受け取らなかった) であるため、外部クエリは次のように評価されます。

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

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

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

サブクエリの種類

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

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

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

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

テーブルに e1 参加しているテーブルが 2 つの異なるロールで表示されるため、テーブルの別名と e2 が必要です。 別名は、次のように、内側のクエリと 1 つ上のレベルのクエリで同じテーブルを参照する入れ子になったクエリでも使えます。

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 への参照が外部クエリの参照と同じ意味を持たないことが明確になります。

IN を使用するサブクエリ

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

USE AdventureWorks2016;
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) を返します。 次に、この値は外側のクエリに置き換えられます。これにより、 のサブカテゴリ識別番号 Production.Productに含まれる製品名が検索されます。

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

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

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

結果セットは次のようになります。

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 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

結果セットは次のようになります。

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 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 とテーブル B をどのような順序で結合しても、得られる答えは同じになります。 サブクエリが関係している場合も同じことが当てはまりません。

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

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

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

このステートメントは結合に変換できません。 類似した等しくない結合は、別の意味を持ちます。完成した自転車ではないサブカテゴリにある製品の名前が見つかります。

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

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

次の例では、ListPrice テーブルの Production.Product 列の値が 2 倍になります。 WHERE 句のサブクエリでは Purchasing.ProductVendor テーブルを参照して、Product テーブルで更新される行を 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 ステートメントを次に示します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SOME、または によって変更されたANY比較演算子ALL

サブクエリを導入する比較演算子は、キーワード ALL または ANY で修飾できます。 SOMEANY に相当する 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 AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID);
GO

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

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

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

いずれかのクエリの結果セットを次に示します。

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" を意味します
  • <> ALLNOT IN と同じ意味になります

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

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

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

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

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

EXISTS を使用するサブクエリ

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

EXISTS を使用して導入するサブクエリの構文は、次のとおりです。

WHERE [NOT] EXISTS (subquery)

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

USE AdventureWorks2016;
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 で作成された一部のクエリは他の方法では表現できませんが、多くのクエリでは、 または によってANYALL変更された比較演算子を使用INして、同様の結果を得ることができます。

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

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

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

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

たとえば、ホイール サブカテゴリに含まれていない製品の名前を見つけるには、次のようにします。

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

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

Transact-SQL では、リスト内を除き、および DELETE ステートメントでINSERTUPDATESELECT式を使用できる任意の場所でサブクエリをORDER BY置き換えることができます。

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

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

関連項目

構文

クエリ パフォーマンスの概念