JOINでの、ONとWHEREでの条件指定に違いはある？

先に結論

目的や評価順序などにおいて違いはある。
一方で取得結果は、内部結合は変わりはないが外部結合では変わる可能性がある。

ONとWHEREの違い

違いを複数の観点でみていきましょう。

観点１： WHERE句とON句の目的

ON句 = 結合条件指定
WHERE句 = 絞り込み条件指定

観点２： SELECT文の評価順序

評価順序は下記のようになっています。

FROM
ON        ←-
JOIN
WHERE     ←-
GROUP BY
HAVING
SELECT...

on句はjoinより先。where句はjoinより後ですね。
ですので、where句に書いた絞り込み条件は、結合後のものに対して行われることがわかります。

観点３： 取得結果

on句に絞り込み条件も入れるパターンと、where句に絞り込み条件を入れるパターンの2つを用意。

-- on句に絞り込み条件も入れるパターン
SELECT
    *
FROM
    table1
    INNER JOIN
        table2
    ON  table1.id = table2.hoge_id AND table1.type = 'XXX'

-- where句に絞り込み条件を入れるパターン
SELECT
    *
FROM
    table1
    INNER JOIN
        table2
    ON  table1.id = table2.hoge_id
WHERE
    table1.type = 'XXX'

上の２つのパターンはどちらも全く同じ結果を返します。
しかし、上の例は内部結合でしたが、外部結合にした場合結果は変わってしまいます。要注意です。

内部結合 → 同じ結果
外部結合 → 違う結果になりうる

詳しくはこちらを参考にしてみてください。
https://style.potepan.com/articles/26226.html

https://atsuizo.hatenadiary.jp/entry/2016/12/12/163921

観点４： 可読性

-- on句に絞り込み条件も入れるパターン
SELECT
    *
FROM
    table1
    INNER JOIN
        table2
    ON  table1.id = table2.hoge_id AND table1.type = 'XXX'

-- where句に絞り込み条件を入れるパターン

SELECT
    *
FROM
    table1
    INNER JOIN
        table2
    ON  table1.id = table2.hoge_id
WHERE
    table1.type = 'XXX'

仮にtable1.type = 'XXX'を絞り込みという目的で使っているのであればon句ではなく、
WHERE句で条件を指定したほうが本来のSQL文の意図に沿っているので伝わりやすいと思います。

ですので、where句かon句に書くか迷った場合は、
絞り込みのためなのか、結合のためなのかを考えると良さそうです。

まとめ

内部結合において、取得結果は変わりませんが外部結合では変わる可能性があります。
上記と可読性を考慮して、結合条件はon句に絞り込み条件はwhere句に記述する、SQLの掟を守るのが良さそうです。

参照

https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/

https://qiita.com/suzukito/items/edcd00e680186f2930a8

https://teratail.com/questions/132380