SQL Server IN vs EXISTS (日本語)

By: Koen Verbeeck｜更新日時：2019-05-13 2019-05-13｜コメント（6）｜関連するもの その他 > T-SQL

Development Best Practices for SQL Server

無料のMSSQLTips Webinarです。

このウェビナーでは、SQL Server の開発のベスト プラクティスについて説明します。

問題

特定の値をフィルタリングするために、WHERE 句で T-SQL の IN 演算子または EXISTS 演算子を使用することに違いはありますか? 論理的な違いなのか、パフォーマンスの違いなのか、それともまったく同じなのでしょうか。 また、NOT INやNOT EXISTSについてはどうでしょうか？

解決策

このヒントでは、EXISTS 演算子と IN 演算子の間に違いがあるかどうかを調べます。 これは、論理的なもの、つまり特定の状況下で異なる動作をするもの、またはパフォーマンス的なもの、つまり一方の演算子を使用することで他方の演算子よりもパフォーマンス上の利点があるかどうかを意味します。 テストクエリにはAdventureWorks DW 2017を使用します。

SQL Server IN vs EXISTS

IN演算子は通常、特定の値のリストに対して列をフィルタリングするために使用されます。 

SELECT , ,FROM ..WHERE IN (1,2);

このクエリは、productcategories Bikes and Categories (ProductCategoryKey 1 and 2)に属するすべての製品サブカテゴリーを検索します。

SQL Server T-SQL IN with static list

サブクエリの結果セット内の値を検索するのに、IN演算子を使用することもできます。 

SELECT , ,FROM ..WHERE IN ( SELECT FROM . WHERE = 'Bikes' );

このクエリは、「Bikes」カテゴリーにリンクされたすべてのサブカテゴリーを返します。

SQL Server T-SQL IN operator with subquery

サブクエリを使用することの利点は、クエリがハードコードされにくくなることです。 IN演算子が機能するためには、副問い合わせが正確に1つの列を返すことが重要です。

EXISTS演算子は、値をチェックするのではなく、行の存在をチェックします。 通常、副問い合わせはEXISTSと一緒に使用されます。

このクエリは ProductSubcategory テーブルのすべての行を返します。なぜなら、最初のサブクエリは行を返すからです（外側のクエリとはまったく関係ありません）。

SELECT , ,FROM ..WHERE EXISTS ( SELECT 1/0 FROM . WHERE = 'Bikes' );

お気づきのように、サブクエリの SELECT 句には 1/0 があります。 通常のクエリでは、これはゼロ除算のエラーを返しますが、EXISTS句の中では、この除算は計算されないため、全く問題ありません。

EXISTSをより意味のある方法で使用するには、相関副問い合わせを使用することができます。相関副問い合わせでは、外側の問い合わせからの値と、内側（副）問い合わせからの値をペアにします。 これにより、外側の問い合わせの値が内側の問い合わせで使用されるテーブルに存在するかどうかが効果的にチェックされます。 例えば、売上を上げたすべての従業員のリストを返したい場合、次のようなクエリを書くことができます。 

SELECT , , ,FROM .. eWHERE EXISTS ( SELECT 1 FROM dbo. f WHERE e. = f. );

EXISTS サブクエリ内の WHERE 句では、外側のテーブルである DimEmployee の従業員キーと、内側のテーブルである FactResellerSales の従業員キーを関連付けています。 両方のテーブルに従業員キーが存在する場合、行が返され、EXISTSはtrueを返します。 従業員キーが FactResellerSales に存在しない場合、EXISTS は false を返し、その従業員は結果から除外されます。

T-SQL EXISTSで営業担当者を探す

IN演算子を使って同じロジックを実装することができます。 

SELECT , , ,FROM .. eWHERE IN ( SELECT FROM dbo. f );

どちらのクエリも同じ結果セットを返しますが、根本的なパフォーマンスの違いがあるのかもしれません。

これはEXISTSのプランです。

SQL Server execution plan for EXISTS

これはINのプランです。

SQL Server execution plan for IN

見た目はまったく同じです。 両方のクエリを同時に実行すると、同じコストが割り当てられているのがわかります。

Compare the SQL Server execution plan for EXISTS vs IN

上の実行プランは EXISTS のもので、下の実行プランは IN のものです。

IO 統計を見てみましょう (SET STATISTICS IO ON というステートメントを実行することで表示できます)。

Compare the SQL Server statistics IO for EXISTS vs IN

このように、証明できるようなパフォーマンスの違いはなく、どちらも同じ結果セットを返しています。 では、どのような場合にどちらか一方を使用するのでしょうか。 以下にガイドラインを示します。

  • 静的な値の小さなリストがある場合 (そして、その値が何らかのテーブルに存在しない場合)、IN 演算子を使用することをお勧めします。
  • 別のテーブルに値が存在するかどうかをチェックする必要がある場合は、クエリの意図を明確に示すことができる EXISTS 演算子が好ましいです。
  • 複数の単一の列に対してチェックする必要がある場合は、IN 演算子では単一の列しかチェックできないため、通常は EXISTS を使用します。 AdventureWorksのデータウェアハウスには、Employeeディメンションがあります。
    employees manage a territory

    さて、営業担当者が他のテリトリーでも販売を行う可能性があります。たとえば、北東地域を担当するMichael Blytheは、4つの異なる地域で販売を行っています。

    マネージャー1人分の地域

    ここで、販売地域のマネージャーの売上金額を、自分の地域だけで調べたいとします。 

    SELECT f. ,f. ,SUM()FROM . fWHERE EXISTS ( SELECT 1 FROM . e WHERE f. = e. AND f. = e. AND e. <> 11 -- the NA region )GROUP BY f. ,f.;

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

    using exists when matching on multiple columns

    EXISTS句の中で、NA地域にリンクされたすべての従業員をフィルタリングして、販売地域のマネージャーを取得します。 外側のクエリでは、内側のクエリで従業員と地域が見つかった場合に、販売地域と従業員ごとのすべての売上を取得します。 このように、EXISTSを使用すると、INではできない複数の列のチェックを簡単に行うことができます。

    SQL Server NOT IN vs NOT EXISTS

    演算子の前にNOT演算子を付けることで、その演算子のブール出力を否定します。 例えば、NOT INを使用すると、リストで見つからない値を持つすべての行を返します。

    Using SQL Server NOT IN Clause

    ただし、NULL値が登場する場合は特別なケースがあります。 リストにNULL値が含まれている場合、結果セットは空になります。

    SQL Server NOT IN with NULLs

    つまり、副問い合わせの結果セットに突然NULL値が現れた場合、NOT INは予想外の結果を返す可能性があります。 NOT EXISTS では、何が返されるかは問題ではないため、この問題はありません。 空の結果セットが返された場合、NOT EXISTSはこれを否定し、現在のレコードがフィルタリングされないことを意味します。

    SQL Server NOT EXISTS with empty result set

    上記のクエリは、販売を行っていないすべての従業員を返します。 論理的には、NOT IN と NOT EXISTS は同じです。つまり、NULLS が含まれていない限り、同じ結果セットを返すことになります。 パフォーマンスに違いはありますか？

    NOT IN および NOT EXISTS で NULL 値を使用しない場合の SQL Server クエリ プラン

    IO 統計についても同様です。

    SQL Server IO statistics with NOT IN and NOT EXISTS

    ただし、1つ問題があります。 先に示したように、NOT INはNULLが関係する場合に問題があります。 もしEmployeeKeyをnullableに変更すると、以下のような実行計画になります。

    NOT IN と NOT EXISTS で NULL 値を使用した場合の SQL Server クエリ プラン

    今回はかなりの違いがあります。 SQL ServerがNULL値を考慮しなければならなくなったため、実行計画が変わりました。

    SQL Server IO statistics with Nullable column

    これで、NOT IN と NOT EXISTS の間に実際のパフォーマンスの違いが出てきました。 いくつかのガイドラインがあります。

    • IN および EXISTS と同様のガイドラインを適用できます。 小さな静的リストに対するチェックでは、NOT INが好ましい。 別のテーブルに存在するかどうかをチェックする場合 NOT EXISTSの方が良いでしょう。 複数の列に対してチェックする場合は、やはりNOT EXISTSです。
    • カラムの 1 つが nullable である場合、NOT EXISTS が望ましいです。

    UUsing Joins Instead of IN or EXISTS

    同じロジックをジョインでも実装できます。 INやEXISTSの代わりには、INNER JOINがあり、NOT INやNOT EXISTSの代わりには、NULL値をチェックするWHERE句を持つLEFT OUTER JOINが使用できます。 これらが全く同じ結果セットと実行計画を返すにもかかわらず、このヒントに含まれていないのは、その意図が異なるからです。 INとEXISTSでは、別のレコード・セットに値が存在するかどうかをチェックします。 結合では、結果セットをマージし、他のテーブルのすべての列にアクセスすることになります。 存在を確認することは、どちらかというと「副作用」にあたります。 (NOT) INや(NOT) EXISTSを使用すると、クエリの意図が明確になります。 一方、Joinには複数の目的があります。

    INNER JOINを使用すると、2番目のテーブルに複数のマッチがある場合、同じ値に対して複数の行を返すこともできます。

    次のステップ
    • このオーバービューでは、さらに多くのT-SQLのヒントを見つけることができます。
    • 長年のMVPであるGail Shaw氏は、EXISTS vs IN vs JOINSに関する素晴らしいシリーズを発表しています。 もしあなたがEXISTS/INとJOINSの比較に興味があるなら、次のブログ記事を読むことができます：
      • IN vs INNER JOIN
      • LEFT OUTER JOIN vs NOT EXISTS
    • SQL Server Join Tips
    • Tip:SQL Server Join Example

    最終更新日：2019-05-05 17:00 2019-05-13

    get scripts

    next tip button

    著者について
    MSSQLTips 著者 Koen Verbeeck Koen Verbeeck は BI の専門家です。 Microsoft BIスタックを専門とし、特にSSISを愛しています。
    全てのTipsを見る
    関連リソース

    • More Database Developer Tips…

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です