SQLのEXISTSをできるだけわかりやすく説明する

2021-03-02

こんにちはー。

SQL書いてますか？私は書いてます。

この記事では、「SQLよくわからんよー」な人向けに、SQLの文法の中でもわかりにくい、EXISTSの使い方について、できるだけわかりやすく説明したいと思います。

まずはサンプル

EXISTSを使ったSQLの例を書いてみます。

select
  *
from
  users
where
  exists (
    select
      1
    from
      purchases
    where
      purchases.user_id = users.id
      and purchases.item_id = 2
      and purchases.shop_id = 5
  )
;

はい。
ユーザーが商品を購入するとpurchasesテーブルにレコードが作られて、
purchaseテーブルの user_idにはユーザーのID, item_idには商品ID, shop_idには店舗IDが入る
という状況をイメージしたSQLです。
これで商品ID: 2の商品を、店舗ID: 5の店舗で購入したユーザーを取得できることになります。

SQLに慣れていないとマジ意味わからんとなりますよね。その気持ち分かります。

これからこのSQLが理解できるように説明していきたいと思います。

EXISTSの動きのイメージ

EXISTSの動作をざっくり説明すると、

「このレコードの値を使って検索したら、レコードはあるかな…？あったらOK」

という感じです。

先ほどのSQLを図を使って説明してみます。

まずは取得したいデータが入っている大元の、usersテーブルがありますね。

で、別のところにpurchasesテーブルがあります。

usersテーブルに対してwhere existsとやると、usersテーブルのレコード1件1件に対して、
「このレコードの値を使って検索したら、レコードはあるかな…？あったらOK」
の判定を行うことになります。

今データを取ろうとしているusersテーブルから一旦離れて、別のテーブルを見に行く感じです。

先に出したSQLでいうと、

「purchasesテーブルの中に、usersテーブルのidとuser_id が一致していて、item_id = 2でshop_id = 5のレコードはあるかな？」

を判定することになります。

例えば、usersテーブルのid = 3のレコードについては、
「user_id = 3で、item_id = 2でshop_id = 5のレコードはあるかな？」となります。

一致するレコードがあれば、そのusersテーブルのレコードは残され、無ければ除外されます。

その結果、条件に一致して残ったレコードがSQLの結果として取得されるという訳です。

SQLの意味をもう少し詳しく

判定の条件をexistsの後の()の中に書きます。

判定の条件の書き方は、SQLのSELECT文と同じ書き方で書きます。

select
  1
from
  purchases
where
  purchases.user_id = users.id
  and purchases.item_id = 2
  and purchases.shop_id = 5

この部分だけ抜き出してみると、普通にselectするときとは少し変わった形になっています。

まずは、select 1となっているのはなぜか？と気になるかもしれません。

これは、「selectする値はなんでもいいので、とりあえず1を入れただけ」です。

SELECT文の文法では、selectの後ろに何かしらを指定しないといけないんですが、
これはレコードがあるかどうかの判定に使いたいだけなので、SELECTした結果の値は別に使いません。

なので、1でもidでも'hogehoge'でも何を書いてもいいです。
個人的にはいつも1を書いてます。

あとは、ここがあんまり見ない形ですね。

purchases.user_id = users.id

このSQLは、from purchasesとなっているので、purchasesテーブルを検索する条件が書いてあるはずです。

purchases.user_idの部分はまあいいですよね。
そのまま、取得しようとしているpurchasesテーブルのuser_idカラムを比較しますよーということです。

で、もう一方のusers.idについては、先ほどusersテーブルの1件1件に対して、「このレコードはあるかな？」を判定すると書きましたが、その比較をするときの1件ごとのusers.idが入る感じです。

なので、usersテーブルに例えばid = 1, id = 2, id = 3のレコードがあれば、
purchases.user_id = 1, purchases.user_id = 3, purchases.user_id = 3
とそれぞれの値を入れた条件で検索する形になります。
（あくまで内部の動きのイメージですよ）

残りの条件も合わせると、例えばusersテーブルのid = 1のレコードについて判定するときは、

where
  purchases.user_id = 1
  and purchases.item_id = 2
  and purchases.shop_id = 5

という形になって、purchasesテーブルを検索する条件になりますね。

usersテーブルに実際にどんな値が入っているかはわからないので、
purchases.user_id = users.idと書くことで、全部のレコードをまとめて指定する感じの意味合いになります。

他のSQLの例

他にもありそうなSQLの例を書いて説明してみます。

select
  *
from
  authors
where
  exists (
    select
      1
    from
      articles
    where
      articles.author_id = authors.id
      and articles.category = 'cat'
  )
;

はい。よくある著者と記事の関係ですね。

existsの中で、articlesテーブルを見に行ってます。
カテゴリー: 猫の記事を書いた著者が取得できるんでしょうね。

続いてはnot existsを書いてみます。

select
  *
from
  users
where
  not exists (
    select
      1
    from
      gate_logs.user_id = users.id
      and gate_logs.room_id = 7
      and gate_logs.direction = 'in'
  )
;

妄想ですが、部屋に入退室を記録する装置があって、gate_logsテーブルに人のID, 部屋のID, 入退室の方向が記録されていくみたいな状況を想定したSQLです。

not existsなので、「なかったらOK, あったらNG」です。
not existsの中の条件は、「部屋ID: 7」で、「方向が入室」のログを検索するようになっているので、つまりはID: 7の部屋に入った記録が無いユーザが取得されることになります。

ところで、EXISTSと同じような結果を取得するのに、JOINを使うこともできます。
が、EXISTSを使えるのであればEXISTSでやったほうがパフォーマンスが良いことが多いです。

select
  *
from
  users
  left join
  gate_logs
    on
      gate_logs.user_id = users.id
      and gate_logs.room_id = 7
      and gate_logs.direction = 'in'
where
  gate_logs.user_id = null
;