SQL Server - EXISTS

提供：MochiuWiki : SUSE, EC, PCB
ナビゲーションに移動 検索に移動

概要

以下の例は、EXISTS句を使用している。
WHERE句の後にEXISTS句があり、EXISTS句内でSQL文を記述する。

これは、副問い合わせである。

 SELECT *
 FROM <テーブル名>
 WHERE EXISTS (SELECT * FROM <テーブル名>);


この時、外部クエリにあるテーブルとEXISTS句内のSQL文にあるテーブルを結合するかどうかで抽出されるデータが異なる。

  • 結合する場合 (相関副問い合わせ)
    外部クエリを実行した後、EXISTS句内のSQL文を実行する。

  • 結合しない場合 (存在判定)
    EXISTS句内のSQL文 (内部クエリ) で値が存在する時、外部クエリが実行される。
    EXISTS句内のSQL文 (内部クエリ) で値が存在しない時、外部クエリは実行されない。



IN句とEXISTS句の違い

パフォーマンスは、データベースエンジンの最適化機能に依存する。
また、インデックスの有無や統計情報によって実行計画が変わる可能性がある。

また、多くのデータベースエンジンでは、IN句とEXISTS句を内部的に相互に変換することがある。

IN句

サブクエリの結果を全て評価 (全走査) する。

サブクエリの結果をメモリ上に一時テーブルとして保持する必要がある。
そのため、サブクエリの結果セットが小さい場合に効率的である。

 /* IN句の場合 */
 SELECT *
 FROM orders 
 WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Korea')


EXISTS句

条件に一致するレコードが見つかった時点で評価を終了する。(Semi-join)

結果セットをメモリに保持する必要がない。
そのため、サブクエリの結果が大きい場合に効率的である。

 /* EXISTS句の場合 */
 SELECT *
 FROM orders o 
 WHERE EXISTS (SELECT 1 FROM customers c 
               WHERE c.id = o.customer_id AND c.country = 'Japan')


EXPLAIN PLAN (実行計画)

EXPLAIN PLANは、クエリがどのように実行されるかを確認するための機能である。

EXPLAIN PLANの読み方を以下に示す。

  • 実行順序
    右から左、上から下に読む。
  • 矢印の太さ
    データ量の大きさを表す。
  • コストの%
    各操作の相対的なコストを示す。
  • 実際の操作
    Table Scan、Index Seek、Sort等の具体的な処理内容


EXPLAIN PLANにおいて、以下に示す情報を基に、必要に応じてインデックスの作成やクエリの書き換え等のチューニングを行う。

  • テーブルスキャンの発生
  • 適切なインデックスの使用
  • 予想される行数
  • 高コストな操作


 /* 一般的な記述 */
 SET SHOWPLAN_XML ON;  -- または
 SET SHOWPLAN_TEXT ON; -- または
 SET STATISTICS PROFILE ON;
 
 SELECT * FROM Users WHERE Age > 20;
 
 SET SHOWPLAN_XML OFF; -- または
 SET SHOWPLAN_TEXT OFF; -- または
 SET STATISTICS PROFILE OFF;



 /* より詳細な情報が欲しい場合 */
 /* I/O情報も含めた詳細な統計情報 */
 SET STATISTICS IO ON;
 SET STATISTICS TIME ON;
 
 SELECT *
 FROM Users
 WHERE Age > 20;
 
 SET STATISTICS IO OFF;
 SET STATISTICS TIME OFF;



 /* クエリを実行せずに実行プランのみ表示する場合 */
 /* この場合、Management Studioの実行プランボタン ([Ctrl] + [L]キー) を押下、または */
 /* クエリの前にEXPLAINと記述する代わりに実行プランボタンを使用する */
 SELECT *
 FROM Users
 WHERE Age > 20;
 GO



テーブルを結合する場合 (相関副問い合わせ)

下表に示す2つのテーブルがある。

T_Employeeテーブル
ID Name Alpha
1 鈴木 Suzuki
2 田中 Tanaka
3 佐藤 Sato


T_Salesテーブル
ID Name
2 Orange
4 Apple


以下の例では、EXISTS句内において、T_SalesテーブルのIDとT_EmployeeテーブルのIDで結合している。

 SELECT * FROM T_Employee
 WHERE Alpha LIKE '%a%' AND
 EXISTS
 (
    SELECT 1 FROM T_Sales
    WHERE T_Sales.ID = T_Employee.ID
 );


1行目は、T_Employeeテーブルのレコードを抽出している。
6行目は、T_SalesテーブルのIDとT_EmployeeテーブルのIDで結合している。

EXISTS句内の副問い合わせに、外側のSQL文(主問い合わせ)のT_Employee.IDカラムを使用している。(SQL文は相関副問合せになる)
この場合、外側のSQL文(主問い合わせ)を実行した後、そこで抽出したレコードでEXISTS句内(副問い合わせ)のSQL文を実行する。

5行目のSELECT文の後の項目(1)は使用されない。(任意の値を設定できる)

結果は、以下の通りである。

ID     Name     Alpha
2      田中     Tanaka


まず最初に、外側のSQL文が実行されてT_EmployeeテーブルのAlphaカラムにaがある2行が抽出される。
次に、T_EmployeeテーブルとT_Salesテーブルの両方に存在するIDカラムが2のT_Employeeテーブルのレコードが抽出される。


テーブルを結合しない場合 (存在判定)

下表に示す2つのテーブルがある。

T_Employeeテーブル
ID Name Alpha
1 鈴木 Suzuki
2 田中 Tanaka
3 佐藤 Sato


T_Salesテーブル
ID Name
2 Orange
4 Apple


EXISTS句内で、上記のT_SalesテーブルでIDカラムを指定する。

 SELECT * FROM T_Employee
 WHERE EXISTS
 (
    SELECT * FROM T_Sales
    WHERE T_Sales.ID = 2
 )
 AND Alpha = 'sato';


1行目は、T_Employeeテーブルのレコードを抽出している。
2～6行目は、EXISTS句のSQL文で、条件でT_SalesテーブルのIDカラムに2を指定している。
この時、EXISTS句内のSQL文を実行した後、レコードが存在する場合は、外側のSQL文が実行される。

結果は、以下の通りである。

ID     Name     Alpha
3      佐藤     Sato


T_SalesテーブルのIDカラムにおいて、2は存在するため、外側のSQL文を実行した後、T_EmployeeテーブルのAlphaカラムのSatoのレコードが抽出される。
例えば、T_SalesテーブルのIDカラムにおいて、5を指定した場合は、外側のSQL文は実行されずにレコードは1件も抽出されない。


「http://mochiuwiki.e2.valueserver.jp/index.php?title=SQL_Server_-_EXISTS&oldid=12516」から取得