単一ローのサブクエリと複数ローのサブクエリ

外部の文に 1 つまたは 0 個のローを返すサブクエリを、「単一ローのサブクエリ」と呼びます。比較演算子の有無にかかわらず、単一ローのサブクエリは SQL 文のどこでも使用できます。

たとえば、単一ローのサブクエリは、SELECT 句の式で使用できます。

SELECT (select FIRST T.x FROM T) + 1 as ITEM_1, 2 as ITEM_2,...

また、単一ローのサブクエリは、比較演算子とともに SELECT 句の式で使用できます。

例：

SELECT IF (select FIRST T.x FROM T) >= 10 THEN 1 ELSE 0 ENDIF as ITEM_1, 2 as ITEM_2,...

外部の文に複数のロー (ただしカラムは 1 つだけ) を返すサブクエリを、「複数ローのサブクエリ」と呼びます。複数ローのサブクエリは、IN 句、ANY 句、ALL 句または EXISTS 句で使用されるサブクエリです。

例 1：単一ローのサブクエリ

テーブル Products に製品だけの情報を、別のテーブル SalesOrdersItems には注文関連の情報を保存します。Products テーブルにはさまざまな製品の情報が入っています。SalesOrdersItems テーブルには、顧客の注文についての情報が入っています。在庫数が 50 未満になったときに製品を再注文する場合、次のクエリで「在庫数が少ない製品は何か」という問い合わせに対する回答を得ることができます。

SELECT ID, Name, Description, Quantity
FROM Products
WHERE Quantity < 50;

ただし、ほとんど注文されない製品がわずかしかないことよりも頻繁に購入される製品が少ないことの方が関心が高いため、製品が注文される頻度を考慮すると便利です。

サブクエリを使用してある顧客が注文する品目の平均数を決定し、その平均をメインクエリに使用して在庫数が少ない製品を検索します。次に示すクエリは、顧客が注文した各タイプの平均品目数の 2 倍未満の製品名とその説明を検索します。

SELECT Name, Description
FROM Products WHERE Quantity <  2 * (
   SELECT AVG( Quantity )
   FROM SalesOrderItems
   );

WHERE 句では、クエリ結果に含まれる、FROM 句にリストされるテーブルからローを選択するのにサブクエリが役立ちます。HAVING 句では、クエリ結果に含まれる、メインクエリの GROUP BY 句で指定されるローのグループを選択するのに役立ちます。

例 2：単一ローのサブクエリ

次の単一ローのサブクエリの例では、Products テーブルの製品の平均価格を計算します。そしてその平均は外部クエリの WHERE 句に渡されます。外部クエリは、平均より低い価格のすべての製品の ID、Name、UnitPrice を返します。

SELECT ID, Name, UnitPrice
FROM Products
WHERE UnitPrice <
  ( SELECT AVG( UnitPrice ) FROM Products )
ORDER BY UnitPrice DESC;

ID	Name	UnitPrice
401	Baseball Cap	10.00
300	Tee Shirt	9.00
400	Baseball Cap	9.00
500	Visor	7.00
501	Visor	7.00

例 3：IN を使用した単純な複数ローのサブクエリ

在庫数が少ない品目を識別し、一方でそれらの品目に対する注文も識別したいとします。次のように、WHERE 句にサブクエリを含む SELECT 文を実行します。

SELECT *
FROM SalesOrderItems
WHERE ProductID IN
   (  SELECT ID
       FROM Products
       WHERE Quantity < 20 )
ORDER BY ShipDate DESC;

この例では、サブクエリは Products テーブル内の ID カラムにおいて WHERE 句の探索条件を満たすすべての値のリストを作成します。そして一連のローが返されますが、返されるカラムは 1 つだけです。IN キーワードは、それぞれの値をセットのメンバーとして扱い、メインクエリ内の各ローがセットのメンバーかどうかをテストします。

例 4：IN、ANY、ALL の使用を比較する複数ローのサブクエリ

SQL Anywhere サンプルデータベースには、経理に関するデータを格納するテーブルが 2 つあります。FinancialCodes テーブルは、経理データとこれらの意味についてのさまざまなコードが入っているテーブルです。FinancialData テーブルから歳入項目をリストするには、次のクエリを実行します。

SELECT *
FROM FinancialData
WHERE Code IN
    ( SELECT Code
        FROM FinancialCodes
        WHERE type = 'revenue' );

Year	Quarter	Code	Amount
1999	Q1	r1	1023
1999	Q2	r1	2033
1999	Q3	r1	2998
1999	Q4	r1	3014
2000	Q1	r1	3114
...	...	...	...

ANY キーワードと ALL キーワードも同様の方法で使用できます。たとえば、次のクエリは前述のクエリと同じ結果を返しますが、ANY キーワードを使用しています。

SELECT *
FROM FinancialData
WHERE FinancialData.Code = ANY
   (  SELECT FinancialCodes.Code
       FROM FinancialCodes
       WHERE type = 'revenue' );

=ANY 条件は IN 条件とまったく同じですが、ANY を < や > などの不等号とともに使用すると、サブクエリをより柔軟に使用できます。

ALL キーワードは ANY に似ています。たとえば、次のクエリは歳入以外の経理データをリストします。

SELECT *
FROM FinancialData
WHERE FinancialData.Code <> ALL
   (  SELECT FinancialCodes.Code
       FROM FinancialCodes
      WHERE type = 'revenue' );

このクエリは、NOT IN を使用した場合の次の文と同じです。

SELECT *
FROM FinancialData
WHERE FinancialData.Code NOT IN
   (  SELECT FinancialCodes.Code
      FROM FinancialCodes
      WHERE type = 'revenue' );

DocCommentXchange で意見交換できます