LEFT JOINでハマった話 - エキサイト TechBlog.

こんにちは、エキサイト株式会社の平石です。

今回は、初歩的な内容でありながら、SQLでLEFT JOINを利用した際にハマったことを記事にしたいと思います。

なお、本ブログのSQLはMySQL 8.2で動作確認をしています。

例

以下のような3つのテーブルがあるとします。

sample_userテーブル・・・ユーザーIDとユーザ名を管理
prefectureテーブル・・・都道府県IDと都道府県名を管理
user_prefectureテーブル・・・ユーザーの居住する都道府県を管理

そして、これらのテーブルにはそれぞれ以下のようなレコードが入っているものとします。

sample_userテーブル

user_id	name
1	佐藤
2	鈴木
3	高橋
4	太田
5	山本
6	中川

prefectureテーブル

prefecture_id	prefecture_name
1	東京
2	神奈川
3	岡山

user_prefectureテーブル

user_id	prefecture_id
1	1
4	1
5	2
6	3

`INNER JOIN`の場合

まずは、関東圏に住んでいるユーザーのみを、都道府県名とともにSELECTすることを考えます。

そのためのSQLは、例えば以下の通りです。

SELECT 
    sample_user.user_id, 
    sample_user.name, 
    prefecture.prefecture_name

FROM sample_user

INNER JOIN user_prefecture
    ON user_prefecture.user_id = sample_user.user_id

INNER JOIN prefecture
    ON prefecture.prefecture_id = user_prefecture.prefecture_id

WHERE prefecture.prefecture_id IN (1, 2);

都道府県を登録していないユーザーは無視して良いのでINNER JOINを利用してsample_userテーブル、user_prefectureテーブル、prefectureテーブルを結合しています。
そして、WHERE句で関東圏の都道府県（ここでは、東京と神奈川）を指定しています。

結果は以下の通りです。

user_id	name	prefecture_name
1	佐藤	東京
4	太田	東京
5	山本	神奈川

先ほどは、都道府県の条件をWHERE句で指定しましたが、prefectureとの結合条件に含めることで見通しが良くなります。

SELECT 
    sample_user.user_id, 
    sample_user.name, 
    prefecture.prefecture_name

FROM sample_user

INNER JOIN user_prefecture
    ON user_prefecture.user_id = sample_user.user_id

INNER JOIN prefecture
    ON prefecture.prefecture_id = user_prefecture.prefecture_id
    AND prefecture.prefecture_id IN (1, 2);

user_id	name	prefecture_name
1	佐藤	東京
4	太田	東京
5	山本	神奈川

`LEFT JOIN`の場合

ここからが本題です。
次は、関東圏に住んでいるユーザーと都道府県を登録していないユーザーをSELECTします。
ただし、都道府県を登録しているユーザーはその都道府県名とともに取得するとします。

そのためのSQLは、INNER JOINをLEFT JOINに変更すれば良さそうです。

SELECT 
    sample_user.user_id, 
    sample_user.name, 
    prefecture.prefecture_name

FROM sample_user

LEFT JOIN user_prefecture
    ON user_prefecture.user_id =sample_ user.user_id

LEFT JOIN prefecture
    ON prefecture.prefecture_id = user_prefecture.prefecture_id
    AND prefecture.prefecture_id IN (1, 2);

しかし、このSQLではうまくいきません。

user_id	name	prefecture_name
1	佐藤	東京
2	鈴木
3	高橋
4	太田	東京
5	山本	神奈川
6	中川

user_id = 6の中川は岡山県在住ですが、都道府県を登録していないユーザーであるかのように取得してしまっています。
INNER JOINの場合は結合条件に記述することでうまくいきましたが、これは一体どういうことでしょうか。

とはいえ、この理由はLEFT JOINの定義から明らかです。
LEFT JOINは結合条件（ON以下の文）に合致したレコード同士を結合した上で、合致しなかった場合も結合の「左側」のテーブルのレコードは全て残します。
この時、結合の「右側」のテーブルのカラムは全てnullとして扱います。

この例の場合は、「鈴木」、「高橋」は

user_prefecture.user_id = sample_user.user_id

となるレコードがuser_prefectureに存在しません。
また、「中川」も2つ目の結合で結合条件prefecture.prefecture_id IN (1, 2)に合致しないため、prefectureテーブルのカラムがnullになった上で選択されます。

prefecture.prefecture_id IN (1, 2)が先に実行され、prefectureテーブル内のprefecture_idが1, 2のレコードが残った状態でLEFT JOINが実行されると、勝手に勘違いしてしまっていました.....。

では、「中川」が選択されないようにするためにはどうすれば良いのでしょうか。
今回の場合は、WHERE句に条件を記述する必要があります。
当然ながらINNER JOINの時と同様に

WHERE prefecture.prefecture_id IN (1, 2)

と記述すると、都道府県を登録していないユーザーが取得されません。

都道府県を登録していない場合と、都道府県を登録している場合で場合分けをして条件を記述する必要があります。

WHERE prefecture.prefecture_id IS NULL
    OR prefecture.prefecture_id IN (1, 2);

user_id	name	prefecture_name
1	佐藤	東京
2	鈴木
3	高橋
4	太田	東京
5	山本	神奈川

CASE式を利用することもできます。

WHERE CASE
          WHEN prefecture.prefecture_id IS NOT NULL
          THEN prefecture.prefecture_id IN (1, 2)
          ELSE TRUE
      END;

終わりに

今回は、LEFT JOINでハマった内容をブログとして残しました。
定義に立ち返ると何ということはない話なのですが、INNER JOINと同じ感覚で使ってしまうと思わぬバグを生むことになりそうです。

では、また次回。

例

INNER JOINの場合

LEFT JOINの場合

終わりに

`INNER JOIN`の場合

`LEFT JOIN`の場合