SQLのEXISTSの挙動

Question

表題の通りですが、SQLのEXISTSの挙動について確認したいです。

以下のようなテーブルがあります

user
→追記(2020/10/04)
+------+--------+
| id | name |
+------+--------+
| 1 | 山田 |
| 2 | 鈴木 |
| 3 | 加藤 |
| 4 | 田中 |
| 5 | 小林 |
+------+--------+
user_sub
→追記(2020/10/04)
+------+--------------------+
| id | job |
+------+--------------------+
| 1 | プログラマー |
| 2 | プログラマー |
| 3 | 営業 |
| 4 | 営業 |
| 5 | 営業 |
+------+--------------------+

これらに対して以下のクエリを実行します。

SELECT * FROM user WHERE EXISTS (
    SELECT * FROM user_sub
      WHERE user.id = user_sub.id AND user_sub.job = "プログラマー"
  );

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

+------+--------+
| id | name |
+------+--------+
| 1 | 山田 |
| 2 | 鈴木 |
+------+--------+

これについて以下の①～④の認識があっているか確認したいです。

先ほどのクエリに記号を振ります

SELECT * FROM user(※A) WHERE EXISTS (
    SELECT * FROM user_sub(※C)
      WHERE user(※B).id = user_sub.id AND user_sub.job = "プログラマー"
  );

①
user(※A)とuser(※B)は完全に同一のテーブルのを指していて(※④で後述します)、
WHERE user(※B).id = user_sub.id AND user_sub.job = "プログラマー"の部分で
joinと同じ手順で結合処理を行い、以下のような表示になる

+------+--------++------+--------------------+
| id | name || id | job |
+------+--------++------+--------------------+
| 1 | 山田 || 1 | プログラマー |
| 2 | 鈴木 || 2 | プログラマー |
| 3 | 加藤 || 3 | 営業 |
| 4 | 田中 || 4 | 営業 |
| 5 | 小林 || 5 | 営業 |
+------+--------++------+--------------------+

②
職業がプログラマー2件が残る（AND user_sub.job = "プログラマー"）
+------+--------++------+--------------------+
| id | name || id | job |
+------+--------++------+--------------------+
| 1 | 山田 || 1 | プログラマー |
| 2 | 鈴木 || 2 | プログラマー |
+------+--------++------+--------------------+

③
結果として、(※A)の2件が表示される。

④
★後述の部分★
例えば、以下のようなクエリでは

SELECT * FROM user(※A) WHERE IN 何らかのカラム (
    SELECT user.何らかのカラム FROM user(※B)
      JOIN user_sub ON user(※B)とuser_sub(※C)の結合条件
  );

user(※A)とuser(※B)も同じuserテーブルですが、※Bと※Cを結合した上で、それを使用して※Aに対して操作を行っているのでuserテーブルは2回登場(※Aと※B)します。しかし、上述のEXISTSのクエリではEXISTSの中のuser(※B)と外のuser(※A)は同じであり、結合に使用されるuserテーブルと値を取り出すuserテーブルのように分かれていない。
→つまりEXISTS中で結合されたuserテーブルをEXISTSの外でも使用している(1回のみの登場)

これらの①～④の認識はあっていますでしょうか。
答えて頂けると助かります。

Comments

[maisumakun]

user_subテーブルはどれでしょうか？

[Chandler_Bing]

遅くなりました。

追記いたします。

Answer 1

Existsを使用する場合、殆どの場面で相関副問合せが用いられます。
質問の内容はExistsよりも相関副問合せの挙動についてですね。

相関副問合せは、結合のように全ての突合は行いません。
駆動表を順次処理する際に副問合せ部分の問い合わせを行います。

※駆動表の件数が少なくなる場合には、結合より相関副問合せの方がコストは少なくなります。

ですので、④の認識は合っていますが、①～③は若干違います。

尚、Existsを用いると、副問合せの対象が複数あったとしても、条件を満たせば副問合せ自体はそこで終了します。
なので、in句を使って列挙するよりコストは低くなります。

Comments

[Chandler_Bing]

ありがとうございます。

>相関副問合せは、結合のように全ての突合は行いません。

ではどうやって二つのテーブルを紐付けしているのでしょうか。

考え方としては結合と同じで良いが、実際の挙動では全ての結合を行わわず、
値自体は結合した時と等しくなるので結果的に同じと考えて良い的な感じでしょうか。

[sazi]

結合では総当たりですが、相関問合せは駆動表次第です。
結果的に同じ様には出来ますが、挙動が異なるのでコストに差が出ます。

[Chandler_Bing]

了解いたしました。

とりあえずは、'結果'は同じだと認識しておきます。

以上です。
色々とありがとうございました。

Answer 2

前提

• テーブル定義の１つ目を user ２つ目を user_sub と想定して記述します。
• 大抵のDBMSはテーブルの統計情報等を使って、効率よくデータを返せる取得方法を自動的に選択するので、常に説明の通りになるとは限りません。

①〜③
おおよその認識は良いと思いますが、EXISTは、EXISTの次のサブクエリはJOINする場合もありますし、サブクエリの実行中に1件でも該当するレコードを発見したら打ち切るという実行の場合もあります。
これは、DBMSや、テーブルの統計情報等々によって決定されるので、一概にどちらとは言えません（が、もちろん結果は同じです）

④
EXIST句を使った場合は、※Aのuserをサブクエリ内で条件として使用しているので（＝相関性がある）、相関サブクエリと呼ばれます。
が、④のクエリの場合は、（何らかのカラム。のような曖昧な書き方なので断言できませんが）相関性がないので、サブクエリ内の結果が先に展開されることが多いはずです。

SELECT * FROM user WHERE id in (
    SELECT id FROM user
      JOIN user_sub ON user.id = user_sub.id
      WHERE user_sub.job = "プログラマー"
  );  // 1, 2

↓ サブクエリ部が (1,2) と展開される

SELECT * FROM user WHERE id in (1,2); 

Comments

[Chandler_Bing]

ありがとうございます。

回答から察するに、私の④への認識は間違っていないという認識でよろしいでしょうか。

[YakumoSaki]

よいとおもいます！

[Chandler_Bing]

ありがとうございます。
拙い文章でしたが、丁寧に回答頂きありがとうございました。