クエリを解決するためのサブクエリ

サブクエリは、クエリ内のクエリとして定義するのが最適です。サブクエリを使用すると、クエリの実行時に実際に開発される条件のデータ行を選択するクエリを作成できます。より正式には、別のSELECTステートメントの句の1つでSELECTステートメントを使用することです。実際、サブクエリは、別のサブクエリ内にある別のサブクエリ内に含めることができます。サブクエリは、INSERT、UPDATE、およびDELETEステートメント内にネストすることもできます。サブクエリは括弧で囲む必要があります。

サブクエリは、単一の値を返すことを条件として、式が許可されている任意の場所で使用できます。これは、単一の値を返すサブクエリをFROM句リストのオブジェクトとしてリストすることもできることを意味します。サブクエリがFROM句の一部として使用される場合、仮想テーブルまたはビューのように扱われるため、これはインラインビューと呼ばれます。サブクエリは、メインクエリのFROM句、WHERE句、またはHAVING句のいずれかに配置できます。

Oracleでは、WHERE句で最大255のサブクエリレベルをネストできます。FROM句で表現されるサブクエリのネストに制限はありません.3または4レベルを超えてネストされたサブクエリに遭遇することはめったにないため、実際には、255レベルの制限は実際にはまったく制限ではありません。

サブクエリのSELECTステートメントは、通常のクエリまたは外部クエリを開始するために使用されるSELECTステートメントと非常によく似ています。サブクエリの完全な構文は次のとおりです。

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

サブクエリの種類

Single Row Sub Query: 単一行の出力を返すサブクエリ。WHERE条件で使用する場合、これらは単一行比較演算子の使用法を示します。

Multiple row sub query: 複数行の出力を返すサブクエリ。これらは、IN、ANY、ALLなどの複数の行比較演算子を利用します。複数の列を返すサブクエリが存在する場合もあります。

Correlated Sub Query: 相関するサブクエリは、外部クエリによって提供されるデータに依存します。このタイプのサブクエリには、EXISTS演算子を使用して、指定された条件を満たすデータ行の存在をテストするサブクエリも含まれます。

単一行のサブクエリ

単一行のサブクエリは、外部クエリの結果が単一の不明な値に基づいている場合に使用されます。このクエリタイプは正式には「単一行」と呼ばれますが、この名前は、クエリが複数の列を返すことを意味しますが、結果は1行のみです。ただし、単一行のサブクエリは、1つの列のみで構成される結果の1行のみを外部クエリに返すことができます。

以下のSELECTクエリでは、内部SQLは1行、つまり会社の最低賃金のみを返します。次に、この値を使用してすべての従業員の給与を比較し、給与が最低賃金と等しい従業員のみを表示します。

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary) 
		FROM employees);

HAVING句は、クエリのグループ結果を何らかの条件に基づいて制限する必要がある場合に使用されます。サブクエリの結果をグループ関数と比較する必要がある場合は、内側のクエリを外側のクエリのHAVING句にネストする必要があります。

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary) FROM employees)

複数行のサブクエリ

複数行のサブクエリは、親クエリに複数行の結果を返すことができるネストされたクエリです。複数行のサブクエリは、WHERE句とHAVING句で最も一般的に使用されます。複数の行を返すため、セット比較演算子（IN、ALL、ANY）で処理する必要があります。IN演算子は前の章で説明したのと同じ意味を持ちますが、ANY演算子は、指定された値をサブクエリによって返される各値と比較します。 ALLは、値をサブクエリによって返されるすべての値と比較します。

以下のクエリは、単一行のサブクエリが複数の行を返す場合のエラーを示しています。

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

複数行演算子の使用

[> ALL]サブクエリによって返される最大値を超える
[<ALL]サブクエリによって返される最小値未満
[<ANY]サブクエリによって返される最大値未満
[> ANY]サブクエリによって返される最小値を超える
[= ANY]サブクエリによって返される任意の値に等しい（INと同じ）

上記のSQLは、以下のようにIN演算子を使用して書き換えることができます。

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

上記のクエリでは、INはサブクエリから返された部門IDと一致し、メインクエリのIDと比較して、条件を満たす従業員の名前を返します。

上記のクエリには結合の方が適していますが、説明のために、サブクエリが使用されています。

相関サブクエリ

外部クエリが内部クエリによって提供される値に依存する通常のサブクエリとは対照的に、相関サブクエリは、内部クエリが外部クエリによって提供される値に依存するサブクエリです。これは、相関サブクエリでは、外部クエリによって選択される可能性のある行ごとに1回、内部クエリが繰り返し実行されることを意味します。

相関するサブクエリは、複雑な管理の質問に答える結果テーブルを生成できます。

以下のSELECTクエリについて考えてみます。以前に検討されたサブクエリとは異なり、このSELECTステートメントのサブクエリはメインクエリとは独立して解決することはできません。外側のクエリでは、エイリアス名がe1のemployeeテーブルから行が選択されるように指定されていることに注意してください。内部クエリは、エイリアスe2を持つ従業員テーブルの従業員部門番号列（DepartmentNumber）を、エイリアステーブル名e1の同じ列と比較します。

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

複数列のサブクエリ

複数列のサブクエリは、外部クエリに複数の列を返し、外部クエリのFROM、WHERE、またはHAVING句にリストできます。たとえば、次のクエリは、現在の給与が1000から2000の範囲にあり、部門10または20で働いている従業員の履歴の詳細を示しています。

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000 
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

複数列のサブクエリが外部クエリのFROM句で使用されると、外部クエリの他の句から参照できる一時テーブルが作成されます。この一時テーブルは、正式にはインラインビューと呼ばれます。サブクエリの結果は、FROM句の他のテーブルと同様に扱われます。一時テーブルにグループ化されたデータが含まれている場合、グループ化されたサブセットは、テーブル内のデータの個別の行として扱われます。以下のクエリのFROM句について考えてみます。サブクエリによって形成されるインラインビューは、メインクエリのデータソースです。

SELECT * 
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);