SQLのCASE式を使ってテーブル同士のマッチングを行うテクニック

ポイント

• CASE式の大きな利点は 式を評価できること
  • なので、CASE式の中で、BETWEEN、LIKE、<、>と言った述語群を使用できる
  • 特にこれが便利： INとEXISTSはサブクエリを引数に取れるので非常に強力です

実例

イメージ 店舗とその最寄駅データの中間テーブルのようなものをイメージして欲しいです。

店舗id	駅id	最寄駅フラグ
1	22	1
1	23	0
2	5	1
3	123	1
4	34	0
4	35	1
4	36	0
5	23	1

SELECT s.name,
    CASE WHEN s.id IN (
                   SELECT shop_id FROM reserves
                   WHERE created_at BETWEEN '2021-01-01' AND '2021-01-31') THEN 'o' ELSE 'x' END AS '2021/1',
    CASE WHEN s.id IN (
                   SELECT shop_id FROM reserves
                   WHERE created_at BETWEEN '2021-02-01' AND '2021-02-28') THEN 'o' ELSE 'x' END AS '2021/2',
    CASE WHEN s.id IN (
                   SELECT shop_id FROM reserves
                   WHERE created_at BETWEEN '2021-03-01' AND '2021-03-31') THEN 'o' ELSE 'x' END AS '2021/3'
FROM shops AS s

結果

店舗ごと各月の予約のある無し状況のクロス集計表が作れます。（わけわからんクロス表であることはご愛敬）

name	2021/1	2021/2	2021/3
名古屋店	o	o	o
横浜店	o	o	o
新宿店	o	x	x

SQLのEXISTSとinの違い

EXISTSは、存在するかどうかだけを判定するのに対し、INは条件に当てはまるデータを全て抽出します。

また、INはカラム名を指定するのに対して、EXISTSは無指定でOK。

SELECT s.name,
    CASE WHEN EXISTS (
        SELECT r.shop_id FROM reserves AS r
        WHERE r.created_at BETWEEN '2021-01-01' AND '2021-01-31'
        AND r.shop_id = s.id
        ) THEN 'o' ELSE 'x' END AS '2021/1',
    CASE WHEN EXISTS (
        SELECT r.shop_id FROM reserves AS r
        WHERE r.created_at BETWEEN '2021-02-01' AND '2021-02-28'
        AND r.shop_id = s.id
        ) THEN 'o' ELSE 'x' END AS '2021/2',
    CASE WHEN EXISTS (
        SELECT r.shop_id FROM reserves AS r
        WHERE created_at BETWEEN '2021-03-01' AND '2021-03-31'
        AND r.shop_id = s.id
        ) THEN 'o' ELSE 'x' END AS '2021/3'
FROM shops AS s

INとEXISTSでは、EXISTSの方がパフォーマンスが優れています。
なので、テーブルのサイズが大きい場合は、EXISTSを使うようにしましょう。

参照

16-18p

アウトプット100本ノック実施中