(SQL) EXISTS句【相関副問合せ】の使い方 〜存在チェックを実施する〜

本記事では、
SQLでデータの存在チェックを行うために利用される「exists」句について、
MySQLを用いて「exists」句の基本的な使い方をサンプルコードを交えながら紹介していく。

exsitsはmysqlに限らず、oracleなど大抵のDBMSでは備わっている機能なので、しっかりと使い方を押さえておこう。

SQLで利用する「exists」句とは？

SQLにおける「exists」句について、詳細を説明していく。

① exists句はデータの存在チェックで利用される

SQLで「exists」句を利用する目的は
「データの存在チェック」を行うためだ。

特定のテーブルのカラムの値が、別のテーブルのカラムに存在するかどうかを確認したい時、EXISTSを利用する。

戻り値は別のテーブルに存在した場合はtrueを返し、存在しなかった場合はfalseを返却するので、where句での絞り込みの際に使用する構文となる。

② exists句の使い方（相関副問合せ(相関サブクエリ)）

exists句の主な利用方法としては、
相関副問合せ(相関サブクエリ)と呼ばれる方法を使用する。

相関副問合せ(相関サブクエリ)とは、
exists句をwhere条件として記述し、主問合せ(メインクエリ)のテーブル列を参照する方法が一般的だ。

どうゆうことかと言うと、例文を見てもらった方が早いだろう。
構文はこんな感じ。

# existsの構文

SELECT *
  FROM TABLE_A TA
WHERE EXISTS (
  SELECT 1
      FROM TABLE_B TB
      WHERE TB.COL_1 = TA.COL_1
);

WHERE句にEXISTSを書いて、その後にサブクエリを書く。

TABLE_AテーブルのCOL_1カラムの値を軸にして、
TABLE_BテーブルのCOL_1カラムに存在するものを取得すると言うSQLだ。

ここからは実際にMySQL環境におけるexists句の使い方をサンプルコードを交えながら紹介していく。

③ existsを実際に利用したサンプルSQL

サンプルとしては下記の2つのテーブルを用意。

# userテーブル

+------+---------------+
| 　　　　id      | name     |
+------+---------------+
|    1      | たろう　   　　|
|    2      | じろう 　   |
|    3      | さぶろう   |
+------+---------------+

#  orderテーブル

+----------+------------+----------+
| order_id | order_name |  user_id |
+----------+------------+----------+
|        1 | 松屋の牛丼  |        1 |
|        2 | すき家の牛丼 |        1 |
|        3 | 吉野家の牛丼 |        2 |
+----------+------------+---------- +

ではサンプルとして、
お店で注文したことがあるユーザーを抽出する想定でSQLを作成していく。

実際のSQLが下記の通り。

select *
from user 
where exists (
    select *
    from order
    where 
        order.user_id = user.id
);

サンプルSQLを実行した結果が下記。

+------+--------------+
| id   | name          |
+------+---------------+
|    1 | たろう   　　　　　　　　　　 |
|    2 | じろう     　　　　　　　　　|
+------+---------------+

サンプルSQLでは「order」テーブルの「user_id」と一致する「id」が、「user」テーブルに存在するかを確認して、過去に一度でも注文したことのあるユーザーデータを抜き出している。

「order」テーブルの「user_id」列にはidが「1」と「2」のデータが登録されているが、idが「3」のユーザーに関してはデータが登録されていないため、検索結果として出力されていないことが確認できる。

「exists」の否定形 「not exists」

existsで別テーブルに存在するデータかチェックできるのに対し、
not existsとすることで、別テーブルに存在しないデータかチェックできる。

まあexsitsの反対なので、特に覚えづらいと言うこともないだろう。

exists句では該当データが存在する場合には「true」、存在しない場合「false」を返却するのに対し、「not exists」では該当データが存在する場合「false」、存在しない場合は「true」を返却する。

not exists句を利用して注文が登録されていないユーザーだけを抽出する

今度はexistsのサンプルとは逆に、
「order」テーブルに注文が登録されていないユーザーのデータを抽出してみる。

サンプルSQLの変更ポイントはexists句をnot exists句に変更するだけだ。

select * 
from user 
where not exists (
    select * 
    from order
    where order.user_id = user.id
);

実行結果が下記の通り。

+---+-----------+
| id   | name    |
+---+-----------+
|    3 | さぶろう  |
+---+-----------+

exists句のサンプルとは逆に、「sample_order」テーブルに登録されていない、未注文のユーザーのみが抽出されていることが確認できる。

exists句を理解するポイント「結局はtrueかfalseを返すだけ」

exists句を理解するポイントとして、筆者が重点的に主張しておきたい点は、
「結局はtrueかfalseを返しているに過ぎない」という点だ。

以下引用

EXISTS (subquery)

• EXISTSの引数は、任意のSELECT文または副問い合わせです。副問い合わせはそれが何らかの行を返すか否かの決定のために評価されます。もし1つでも行を返すのであれば、EXISTSの結果は“true（真）”となり、副問い合わせが行を返さない場合、EXISTSの結果は“false（偽）”となります。
• この副問い合わせは通常、最後まで実行されず、少なくとも１つの行が返されたかどうかを判定し得るに足りる時点まで実行されます。
• 結果は何らかの行が返されるのかのみに依存し、それらの行の内容には依存しないことから、副問い合わせの出力リストは通常興味のあるものではありません。

https://www.postgresql.jp/document/8.3/html/functions-subquery.html

上記は要約すると、「exists句のクエリは、一行でもレコードが取得できればtrueを返すよ、そうでなければfalseを返すよ。この時、exists句のクエリ自体はぶっちゃけ興味ないよ」ということだ。

つまり何が言いたいかと言いうと、よくexists句の使い方を解説しているサイトはあるものの、大体どれも似たり寄ったりな解説のみで、exists句の本質（結局はtrueを返すかfalseを返すかに過ぎない）というところの説明をしていないサイトが多いなあというのを言いたいのである。

なのでぶっちゃけ以下のようなサンプルSQLも成り立ってしまうのである。

SELECT
    *
FROM
    テーブルA
WHERE EXISTS ( 
    SELECT * FROM テーブルB
    )

exists句の中では、テーブルBのレコードを全取得しているだけであり、テーブルAとテーブルBとの比較などしていない。しかし上記を実行すると、テーブルAのレコードが全て取れてしまう。

もう何を言いたいか理解できたかもしれないが、これは、exists句が必ずレコードを返す内容であり結果としてtrueを必ず返却するため、テーブルAのレコードが全て取れてしまうという結果になっている。

このように、exists句の本質を見抜くことで、「存在チェックを行う」という本来の役割を使いこなせるのではないかと思う次第である。

参考記事

(SQL) 副問い合わせ(サブクエリ)を基礎から理解する【使い方・使える場所】

(SQL) テーブル結合をちゃんと理解する【inner、outer、full、cross join の違い】

参考：https://www.shift-the-oracle.com/sql/exists-condition.html

内部リンク：https://hara-chan.com/it/programming/php-autoload-composer/