相関サブクエリによるEXISTS句の挙動

Question

実現したいこと

【契約テ－ブル】に対して、
「契約終了年が9999かつA2から始まる契約コードを一つでも持っている顧客番号を抽出。しかし、その顧客番号が、契約開始年が2026であるA21111の契約コードを持っていれば、その顧客番号は除外する」
といった条件のSELECT文は以下のようになるとのことです。

SELECT DISTINCT 顧客番号
　FROM 契約テーブル a
　WHERE　 a.契約コード LIKE 'A2%' 
　AND a.契約終了年 = 9999
　AND NOT EXISTS 
　　　(SELECT 1 FROM 契約テーブル b
　　　　　WHERE b.契約コード = 'A21111'
　　　　　AND b.契約開始年 = 2026
　　　　　AND a.顧客番号 = b.顧客番号）

【契約テーブル】
顧客番号,契約コード,契約開始年,契約終了年
111111,A20000,2016,2018
111111,A20000,2018,9999
111111,A21111,2018,9999
111111,A20000,2018,9999
111111,A21111,2026,9999
111112,A21111,2018,9999
111112,A20000,2018,9999
111112,A21111,2024,9999
111113,A41111,2018,9999
111113,A40000,2018,9999
111113,A21111,2024,9999
111114,A41111,2018,9999
111114,A40000,2018,9999
111114,A41111,2026,9999
・
・
・

クエリを実行すると、条件通りの抽出ができ
顧客番号
・111112
・111113
の２行が表示されました。

結果的に欲しいデータが抽出できているので良いのですが、なぜ抽出できているのかが分かりません。
「111112と111113が抽出される」のは理解できますが、**「なぜ顧客番号111111が外されているのか」**が分かりませんでした。

相関副問い合わせを用いたEXISTS句のため、
まず主問い合わせから実行され、顧客番号111111に関しては
・111111,A20000,2018,9999
・111111,A21111,2018,9999
・111111,A20000,2018,9999
・111111,A21111,2026,9999
の4行が副問い合わせでの評価の対象になると思います。

次に副問い合わせによって1行ずつ評価されると思いますが、
・（111111,A20000,2018,9999）は副問い合わせの条件に当てはまらないため、NOT EXISTSによって行が返る
・（111111,A21111,2018,9999）は副問い合わせの条件に当てはまらないため、NOT EXISTSによって行が返る
・（111111,A20000,2018,9999）は副問い合わせの条件に当てはまらないため、NOT EXISTSによって行が返る
・（111111,A21111,2026,99999）は副問い合わせの条件に当てはまるため、NOT EXISTSによって行が返らない

結果的に3行が返ることになり、主問い合わせのDISTINCT 顧客番号によって「111111」は表示されるのではないかと思いました。

恐らく、「a.顧客番号 = b.顧客番号」の部分が関係しており、私の認識が間違っているとは何となくわかっているのですが、正しい考え方が不明です。

ご教示いただけると幸いです…

Comments

[usm2030]

a.顧客番号 = b.顧客番号
のため、顧客番号が111111であれば、全ての111111のデータをサブクエリでは検索することが考えられます。
つまり、顧客番号が111111であり、サブクエリの他の条件も含めて存在する場合は常に111111のデータは取得しません。

・（111111,A21111,2026,99999）は副問い合わせの条件に当てはまるため、NOT EXISTSによって行が返らない

ので、顧客番号が111111の間、WHEREの結果は常にFALSEになります

端的にまとめると
111111,A21111,2026,9999
があるので、
顧客番号がこれと一致する限り常にサブクエリの検索結果が返り、Notで反転するため、常にFALSEになります。

[yyomu]

「a.顧客番号 = b.顧客番号 のため、顧客番号が111111であれば、全ての111111のデータをサブクエリでは検索すること」ここの部分の理解が足りていなかったんですね、、！
理解できました！
ご回答ありがとうございます！

Answer 1

exists は条件等により主表の件数が少なくなる場合に、問い合わせを高速にする目的でinの代わりに相関と共に用いられます。

なので、inで置き換えてみると分かり易くなると思います。

SELECT DISTINCT 顧客番号
FROM  契約テーブル a
WHERE a.契約コード LIKE 'A2%' 
  AND a.契約終了年 = 9999
  AND a.顧客番号 NOT IN (
          SELECT b.顧客番号 FROM 契約テーブル b
          WHERE b.契約コード = 'A21111'
            AND b.契約開始年 = 2026
      ）

Comments

[yyomu]

なるほど！INもEXISTSも用途としてはほぼ同じ？ように使えるですね。（パフォーマンスの違いだけで）

INで置き換えると非常に理解しやすかったです。ありがとうございます。

Answer 2

このような自己結合を含む相関サブクエリは、同じテーブルでも別名をつけて別テーブルとして考えます。

まず主問い合わせから実行され、顧客番号111111に関しては
・111111,A20000,2018,9999
・111111,A21111,2018,9999
・111111,A20000,2018,9999
・111111,A21111,2026,9999
の4行が副問い合わせでの評価の対象になると思います。

この4行は外側のクエリに存在する 契約テーブル a の行として扱います。
そして今回の相関サブクエリは、サブクエリ内にのみ存在する 契約テーブル b に関する条件判定となります。

細かい考え方

ちょっと記述順を入れ替えて、bの条件だと分かりやすくします。
サブクエリ内で用いる 契約テーブル a のカラムは a.顧客番号 のみです。

  AND NOT EXISTS 
      (SELECT 1 FROM 契約テーブル b
          WHERE b.契約コード = 'A21111'
          AND b.契約開始年 = 2026
          AND b.顧客番号 = a.顧客番号)

実行イメージ ステップ1

NOT EXISTS 以外の条件で抽出される 契約テーブル a のデータは次の8行です (※質問文記載の範囲で)

• 111111,A20000,2018,9999
• 111111,A21111,2018,9999
• 111111,A20000,2018,9999
• 111111,A21111,2026,9999
• 111112,A21111,2018,9999
• 111112,A20000,2018,9999
• 111112,A21111,2024,9999
• 111113,A21111,2024,9999

実行イメージ ステップ2

上記aの8行に対する相関サブクエリの実行イメージとしては以下の 2-1 ～ 2-8 の様になります。

※サブクエリ内のaに関する記述をそれぞれの行の値として置き換え、bだけのクエリとして考える

実行イメージ ステップ2-1

aの行 111111,A20000,2018,9999 が対象

AND NOT EXISTS 
    (SELECT 1 FROM 契約テーブル b
        WHERE b.契約コード = 'A21111'
        AND b.契約開始年 = 2026
        AND b.顧客番号 = '111111')   -- '1' を含む行が b から抽出される => `NOT EXISTS` が false 判定になる

実行イメージ ステップ2-2

aの行 111111,A21111,2018,9999 が対象

AND NOT EXISTS 
    (SELECT 1 FROM 契約テーブル b
        WHERE b.契約コード = 'A21111'
        AND b.契約開始年 = 2026
        AND b.顧客番号 = '111111')   -- a.顧客番号が同じなので2-1と同じ結果に

実行イメージ ステップ2-3

aの行 111111,A20000,2018,9999 が対象
※a.顧客番号が同じなので2-1と同じ (省略)

実行イメージ ステップ2-4

aの行 111111,A21111,2026,9999 が対象
※a.顧客番号が同じなので2-1と同じ (省略)

実行イメージ ステップ2-5

aの行 111112,A21111,2018,9999 が対象

AND NOT EXISTS 
    (SELECT 1 FROM 契約テーブル b
        WHERE b.契約コード = 'A21111'
        AND b.契約開始年 = 2026
        AND b.顧客番号 = '111112')   -- この条件に合致する b の行は存在しない => `NOT EXISTS` が true 判定になる

実行イメージ ステップ2-6

aの行 111112,A20000,2018,9999 が対象
a.顧客番号が同じなので2-5と同じ (省略)

実行イメージ ステップ2-7

aの行 111112,A21111,2024,9999 が対象
a.顧客番号が同じなので2-5と同じ (省略)

実行イメージ ステップ2-8

aの行 111113,A21111,2024,9999 が対象

AND NOT EXISTS 
    (SELECT 1 FROM 契約テーブル b
        WHERE b.契約コード = 'A21111'
        AND b.契約開始年 = 2026
        AND b.顧客番号 = '111113')   -- この条件に合致する b の行は存在しない => `NOT EXISTS` が true 判定になる

Comments

[yyomu]

とても細かくご回答ありがとうございました！
非常に参考になりました！

Answer 3

・（111111,A20000,2018,9999）は副問い合わせの条件に当てはまらないため、NOT EXISTSによって行が返る
・（111111,A21111,2018,9999）は副問い合わせの条件に当てはまらないため、NOT EXISTSによって行が返る
・（111111,A20000,2018,9999）は副問い合わせの条件に当てはまらないため、NOT EXISTSによって行が返る
・（111111,A21111,2026,99999）は副問い合わせの条件に当てはまるため、NOT EXISTSによって行が返らない

これが誤りです。顧客番号111111のレコードは全て副問合せの条件に当てはまりません。

まずはEXISTSのクエリだけで考えると良いかと思います。

SELECT *
FROM 契約テーブル AS a
WHERE EXISTS (
  SELECT 1 FROM 契約テーブル AS b
  WHERE b.契約コード = 'A21111'
  AND b.契約開始年 = 2026
  AND a.顧客番号 = b.顧客番号
)

上記のクエリの場合、
b.契約コード = 'A21111' AND b.契約開始年 = 2026 の条件にマッチするテーブルbのレコードは下記の1レコードです。

111111,A21111,2026,9999

このテーブルbのレコードに対し、a.顧客番号 = b.顧客番号 という条件をつけていますから、EXISTSで抽出されるテーブルaのレコードは顧客番号が111111のレコード全てです。

111111,A20000,2016,2018
111111,A20000,2018,9999
111111,A21111,2018,9999
111111,A20000,2018,9999
111111,A21111,2026,9999

クエリはNOT EXISTSになっていますから、質問のデータにおいてNOT EXISTSが返すデータは顧客番号が111111以外、と同等となります。あとは他のWHERE条件によって、111111以外のレコード内から絞り込みが行われ、結果として111112と111113が抽出されています。

Comments

[yyomu]

a.顧客番号 = b.顧客番号という条件があることにより、「111111,A21111,2026,9999」の行が抜き出せれた時点で、顧客番号111111の行全てがEXISTS句で抽出されるのですね！
正直、まだ直感的な理解は浅いですが、原理が分かり大変助かりました。
ありがとうございました