【SQL】JOINに関する誤解と理解

SQL

こんにちは、しきゆらです。

今回は、タイトルに有るようにSQLのJOINに対する個人的な誤解が解けたのでそれをメモしておきます。

前半は事の経緯なので、無視しても大丈夫です。

 

そして、先人である巨人のURLはこちら。

SELECT構文:JOINを使ってテーブルを結合する – SMART

http://rfs.jp/sb/sql/s03/03_3.html#JOIN

また、SQLだけ見せろ!という方向け

今回のSQLサンプル

http://sqlfiddle.com/#!9/0a510a/2/0

 

ことのはじめ

そもそも、私はこれまでSQLでテーブルを作るとき、それぞれのテーブルをどう関連付けて取得すればいいのかわからず1つのテーブルにすべてのデータをぶち込んでいました。

いわゆる非正規の状態。

 

しかし、それではいかん、ということで表を分けるようにしました。

(なお、この段階ではまだJOINと言うものを知りません。)

必要なデータの取得は、Ruby等のプログラムで各テーブルからデータを取得し、結合・ソート等をしていました。

 

しかし、表を結合して必要なデータを表現するためにJOINを用いればいいということを(最近)知り、

何でもかんでもJOINしていたわけです。

 

そんななか、本日ぶつかった壁のお話をしましょう。

 

ほんだい

何でもかんでもテーブルをJOINしてデータを取得していた私。

ぶつかった壁というのは、「JOINすると特定のデータが取得できない」という問題です。

 

例えば、以下のような何かのメモを表すデータベースを考えます。

メモを表現するitemテーブル

[table id=5 /]

 

メモのタグを表現するtagテーブル

[table id=6 /]

 

メモとタグを紐付けるtag_mapテーブル

[table id=7 /]

 

これらをもとに、考えてみます。

 

やりたいこと

3つの表をまとめて、メモの内容とタグ名を一覧で取得したい。

もしタグが付いていなくても、ないことも知りたい。

 

解決案

こんな感じのSQLを書いてみる。

SELECT content, GROUP_CONCAT(tag.name separator ',') as tags FROM item JOIN (tag JOIN tag_map USING(tag_id)) USING(item_id) GROUP BY item_id;

なお、GROUP_CONCATは引数のものをセパレータで区切って結合してくれる関数です。

12.19.1 GROUP BY (集約) 関数 – MySQLリファレンス

https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_group-concat

こうすると、「content」と「tags」をいっぺんに取ってこれます。

やったね!!

 

もんだい

全てにタグが設定されていれば問題なく取得できます。

しかし、新しく以下のようなデータが入ってくると話は変わってきます。

[table id=8 /]

 

上記レコートが追加された状態で同じようにSQLを実行します。

 

変化がない!?Why!?!?!?!!?!?

 

かいせつ

単なるJOINや「from hoge, fuga, …」としてテーブルを結合するものを内部結合(INNER JOIN)と言います。

これは、条件にあったもののみを列挙する結合。

今回であれば、「item_idとtag_idが紐づけされているもの」が条件となります。

 

しかし、itemテーブルに追加したものはまだタグと紐づけされていません。

そうなると、内部結合では条件にマッチしていないため取得されません。

 

 

 

解決方法

この問題を解決するには、外部結合(OUTER JOIN)を使います。

これは、条件にあっていないものも含めて取得するもの。

 

上記SQLを外部結合に書き換えて、実行してみます。

SELECT content, GROUP_CONCAT(tag.name separator ',') as tags FROM item LEFT OUTER JOIN (tag JOIN tag_map USING(tag_id)) USING(item_id) GROUP BY item_id

SQL的には、「JOIN」の前に「LEFT OUTER」をつけただけ。

これだけで外部結合になります。

※LEFT OUTER JOINの他に、RIGHT OUTER JOINと言うものもあるらしいです。

つなげる方向が違うようですが、基本的にはどっちか片方に合わせておけば問題ないかと思います。

LEFTとRIGHTで動作上の違いとか取得時の違いとかがあれば教えてください・・・

 

結果は以下の通り。

きちんと、取得できていることがわかります。

タグがあるものは、タグ名も一緒に取得し、タグがないものはnullとして取得しています。

 

 

参考 SELECT構文:JOINを使ってテーブルを結合する – SMART

http://rfs.jp/sb/sql/s03/03_3.html#JOIN

 

今回のSQLサンプル

http://sqlfiddle.com/#!9/0a510a/2/0

 

まとめ

今回はSQLの内部結合と外部結合の違いを知りました。

条件に合うものだけが必要なのか、そうでないのかによって使い分ける必要が有ることを知りました。

 

まだまだSQLとは仲良くなれていないので、もっとSQLと戯れていきたいです。

 

今回はこのへんで。

おわり

SQL

Posted by shikiyura