SQLのLEFT JOIN XXX ON YYYY AND ZZZZ したらはまった話

要すると、OUTER JOIN(LEFT|RIGHT JOIN)とINNER JOINの違い

何が起きたか

userテーブル

id name
1 user1
2 user2
3 user3
4 user4
5 user5

skillテーブル

id name
1 word
2 excel
3 power point
4 c++ programming
5 web design
6 Linux

user_skillテーブル

id user_id skill_id
1 1 1
2 1 2
3 1 3
4 2 1
5 1 4
6 2 4
7 2 3
8 3 2

とりたいデータが

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4

user1が持っているスキルの一覧と、そのスキル名を出したかった場合です。
これを取得するために

を実行したところ

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4
2 user2 1
2 user2 3
3 user3 2
4 user4 null
5 user5 null

うん、全部出てきちゃった・・・・orz
仕組みを理解すると当然なんですが。

結論からいうと

OUTER JOINとINNER JOINの違いを理解できていませんでした。

(LEFT|RIGHT) JOINはOUTER JOINで外部結合、INNER JOINやFROMで複数テーブルを指定する場合は
INNER JOINで内部結合になってました。

とにかくいえることはJOINの条件指定ONには、結合するテーブル双方に
関連するもののみを書くべき。

上記のようにuserだけに関連する条件をONの内部に
書いたことで、その条件がどこに活きているのか、わかりづらくなった。

ONの中に、結合するテーブルどちらか一方の条件を書いても
期待するデータを取ることはできるが、複数あるJOINの挙動を把握していないとわかりづらくなる。

内部結合と外部結合

外部結合(OUTER JOIN)

ある2つのテーブルの指定した片方に存在するデータを取得し
もう一方にない場合もデータを空にして取得します

上記のテーブルに対して

とした場合は

(左側の)userテーブルから(WHERE句がないので)全件を取得し
その結果のuser.idと、(右側の)user_skillテーブルのuser_idが
等しいものがあれば結合します。

結果は以下のようになります。

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4
2 user2 1
2 user2 3
3 user3 2
4 user4
5 user5

となります。データがないものは空になって取得されます。

と、ON句内にuserテーブルに対する条件を付与しても
LEFT JOIN句の前で既に全件取得しているのでu.id = 1は全く意味を持たない条件となります。
結果として以下のようなデータが出力されます。(前のものと全く同じ)
これが、僕がやってしまった間違いでした。

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4
2 user2 1
2 user2 3
3 user3 2
4 user4 null
5 user5 null

今度はRIGHT JOINを使ってみます。

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4
2 user2 1
2 user2 3
3 user3 2

これに対してRIGHT JOINはuser_skill(右側)の結果に対してuser(左側)の
データを結合します。なので、user_skillにuser_idが入っていないuser4とuser5は
データに含まれていないことがわかります。

それではAND句で条件を追加してみます。

RIGHT JOINの場合はJOIN句が優先されるため、u.idという条件が
SELECT *** FROM user u の結果に影響を与えます。

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4

最初に欲しかったデータになりました。

(ただ、後述しますが、この例はONに書く必要のない条件を
あえて書いているような感じなので、僕はもうこういう書き方したくないです。
メリットがあるならご教授いただきたいです・・・)

内部結合(INNER JOIN)

ある2つのテーブルの両方に存在するデータのみを取得します。

上記のテーブルに対して

を実行すると

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4
2 user2 1
2 user2 3
3 user3 2

このように双方のテーブル両方に存在するデータのみが取得されます。

のようにANDで条件を指定すれば

user.id user.name user_skill.skill_id
1 user1 1
1 user1 2
1 user1 3
1 user1 4

期待どおり、user1のデータのみを取得できます。

これは

と同じです。このように書くことで内部的には
上に書いたINNER JOINがされます。

まとめ

上記の結果から
FROM句で指定されたテーブルをLEFT,JOIN句で指定されたRIGHTと
すると

  1. LEFT JOINのONはLEFTのテーブルには適用されない
  2. RIGHT JOINのONはLEFTのテーブルにも適用される
  3. INNER JOINのONはLEFTのテーブルにも適用される

ということがわかります。

僕の認識としては、ONには2つのテーブル共通の条件のみを
記述するべきだと思いました。

LEFTテーブルのみにかかってくる条件はWHERE句で指定したほうが
統一できて、わかりやすいです。

※SQLに精通しているわけではなく、開発の過程で必要に迫られて
たまに書く程度の知識ですので、もし他のメリットや考慮するべき点が
あれば、アドバイスいただけたらと思います。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です