質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.31%
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

531閲覧

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

yyomu

総合スコア9

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2024/06/01 14:43

実現したいこと

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

SQL

1SELECT DISTINCT 顧客番号 2 FROM 契約テーブル a 3 WHERE  a.契約コード LIKE 'A2%' 4 AND a.契約終了年 = 9999 5 AND NOT EXISTS 6   (SELECT 1 FROM 契約テーブル b 7     WHERE b.契約コード = 'A21111' 8     AND b.契約開始年 = 2026 9     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
の2行が表示されました。

結果的に欲しいデータが抽出できているので良いのですが、なぜ抽出できているのかが分かりません。
「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.顧客番号」の部分が関係しており、私の認識が間違っているとは何となくわかっているのですが、正しい考え方が不明です。

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

usm2030

2024/06/01 19:05

a.顧客番号 = b.顧客番号 のため、顧客番号が111111であれば、全ての111111のデータをサブクエリでは検索することが考えられます。 つまり、顧客番号が111111であり、サブクエリの他の条件も含めて存在する場合は常に111111のデータは取得しません。 ・(111111,A21111,2026,99999)は副問い合わせの条件に当てはまるため、NOT EXISTSによって行が返らない ので、顧客番号が111111の間、WHEREの結果は常にFALSEになります 端的にまとめると 111111,A21111,2026,9999 があるので、 顧客番号がこれと一致する限り常にサブクエリの検索結果が返り、Notで反転するため、常にFALSEになります。
yyomu

2024/06/04 00:05

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

回答3

0

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

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

SQL

1SELECT DISTINCT 顧客番号 2FROM 契約テーブル a 3WHERE a.契約コード LIKE 'A2%' 4 AND a.契約終了年 = 9999 5 AND a.顧客番号 NOT IN ( 6 SELECT b.顧客番号 FROM 契約テーブル b 7 WHERE b.契約コード = 'A21111' 8 AND b.契約開始年 = 2026 9

投稿2024/06/02 05:33

sazi

総合スコア25430

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yyomu

2024/06/02 09:29

なるほど!INもEXISTSも用途としてはほぼ同じ?ように使えるですね。(パフォーマンスの違いだけで) INで置き換えると非常に理解しやすかったです。ありがとうございます。
guest

0

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

まず主問い合わせから実行され、顧客番号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 判定になる

投稿2024/06/01 20:37

pecmm

総合スコア760

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yyomu

2024/06/02 09:27

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

0

ベストアンサー

・(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が抽出されています。

投稿2024/06/01 19:37

編集2024/06/01 19:38
Eggpan

総合スコア3271

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yyomu

2024/06/02 09:32

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.31%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問