【SQL】JOINに関する誤解と理解
こんにちは、しきゆらです。
今回は、タイトルに有るようにSQLのJOINに対する個人的な誤解が解けたのでそれをメモしておきます。
前半は事の経緯なので、無視しても大丈夫です。
そして、先人である巨人のURLはこちら。
SELECT構文：JOINを使ってテーブルを結合する – SMART
また、SQLだけ見せろ！という方向け
今回のSQLサンプル
ことのはじめ
そもそも、私はこれまでSQLでテーブルを作るとき、それぞれのテーブルをどう関連付けて取得すればいいのかわからず１つのテーブルにすべてのデータをぶち込んでいました。
いわゆる非正規の状態。
しかし、それではいかん、ということで表を分けるようにしました。
(なお、この段階ではまだJOINと言うものを知りません。)
必要なデータの取得は、Ruby等のプログラムで各テーブルからデータを取得し、結合・ソート等をしていました。
しかし、表を結合して必要なデータを表現するためにJOINを用いればいいということを(最近)知り、
何でもかんでもJOINしていたわけです。
そんななか、本日ぶつかった壁のお話をしましょう。
ほんだい
何でもかんでもテーブルをJOINしてデータを取得していた私。
ぶつかった壁というのは、「JOINすると特定のデータが取得できない」という問題です。
例えば、以下のような何かのメモを表すデータベースを考えます。
メモを表現するitemテーブル
[table id=5 /]
メモのタグを表現するtagテーブル
[table id=6 /]
メモとタグを紐付けるtag_mapテーブル
[table id=7 /]
これらをもとに、考えてみます。
やりたいこと
３つの表をまとめて、メモの内容とタグ名を一覧で取得したい。
もしタグが付いていなくても、ないことも知りたい。
解決案
こんな感じの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
今回のSQLサンプル
まとめ
今回はSQLの内部結合と外部結合の違いを知りました。
条件に合うものだけが必要なのか、そうでないのかによって使い分ける必要が有ることを知りました。
まだまだSQLとは仲良くなれていないので、もっとSQLと戯れていきたいです。
今回はこのへんで。
おわり