ON 句に検索条件を書くとき

業務でクエリを読んでいたところ、ON 句には結合条件だけではなく検索条件も書けることを知った。WHERE 句に書く場合との違いはあるのかなと調べてみると、外部結合で利用すると抽出結果に差異が生じることがあるので注意する必要があるみたい。

外部結合で WHERE 句に検索条件を記述

SELECT
  * 
FROM
  users 
  LEFT JOIN prefectures 
    ON users.prefecture_id = prefectures.id 
WHERE
  prefectures.id = 5;

+------+---------------+--------+------+-----------+
| id   | prefecture_id | name   | id   | name      |
+------+---------------+--------+------+-----------+
|    1 |             5 | イヌ   |    5 | 秋田県    |
+------+---------------+--------+------+-----------+

外部結合で ON 句に検索条件を記述

SELECT
  * 
FROM
  users 
  LEFT JOIN prefectures 
    ON users.prefecture_id = prefectures.id 
    AND prefectures.id = 5;

+------+---------------+--------+------+-----------+
| id   | prefecture_id | name   | id   | name      |
+------+---------------+--------+------+-----------+
|    1 |             5 | イヌ   |    5 | 秋田県    |
|    2 |             9 | サル   | NULL | NULL      |
|    3 |            33 | キジ   | NULL | NULL      |
+------+---------------+--------+------+-----------+

ON 句の場合は、テーブルを結合する前に検索条件の絞り込みが行われるので、左側の軸となるテーブルのレコードがすべて表示されている。個人的に ON 句には結合条件だけ書くようにして、意図しない結果にならないように気をつけようと思う。