アナログCPU：5108843109

ゲームと音楽とプログラミング（酒と女とロックンロールのノリで）

('ω') ＜ ｲｻﾞﾕｹｴﾝｼﾞﾆｬｰ

ざっくりSQL入門 #5：クエリを書く＜テーブル結合編＞

SQL ざっくりSQL入門シリーズ

※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。

今回はテーブル結合のやりかたについて。


テーブル結合とは？

文字通り、二つのテーブルを結合します。

ここでは例えばツイッター的なサービスを想像してみましょう。
ものすごく単純化して、以下のようなテーブルがあるとします。

  • userテーブル
    • user_id（ID）
    • name（名前）
  • tweetテーブル
    • tweet_id（つぶやきID）
    • user_id（会員ID）
    • text（内容）

それぞれ、中身はこんな感じ。

user_id name
hoge ほげほげ
fuga ふがふが
tweet_id user_id text
1 hoge そろそろ帰りたい
2 fuga たのしいじんせい
3 hoge 会社いきたくない
4 fuga 圧倒的成長
5 fuga やっぱむり
6 piyo ごはんおいしい

これ、ツイッター的なサービスということで、タイムラインのようなものにしますよね。
そうすると、ツイートのようなものだけでなく、ユーザー名を併せて表示するのが普通ではないでしょうか。

しかしその場合、両方のテーブルのデータを使用していますから、
どうやってもひとつのテーブルを用いたSELECT構文では実現できません。
こういうときに使うのが「JOIN」句です。

JOINには「内部結合」と「外部結合」、そして「クロス結合」があるので、
それぞれについて記載していきます。

内部結合

指定した条件で一致したレコード同士を結合します。
これには「INNER JOIN」を使用します。

例えば、前述のテーブルを用いてタイムラインを作る場合、
以下のように記述します。

SELECT
  `tweet`.`tweet_id`
 ,`tweet`.`user_id`
 ,`user`.`name`
 ,`tweet`.`text`
FROM
  `tweet`
INNER JOIN
  `user`
  ON `tweet`.`user_id` = `user`.`user_id`
ORDER BY
  `tweet`.`tweet_id` DESC

この場合の結果セットは以下の通り。

tweet_id user_id name text
5 fuga ふがふが やっぱむり
4 fuga ふがふが 圧倒的成長
3 hoge ほげほげ 会社いきたくない
2 fuga ふがふが たのしいじんせい
1 hoge ほげほげ そろそろ帰りたい

「tweet」テーブルに対して、
「user」テーブルを「各テーブルのuser_idが一致という条件で」結合しています。
その結合した状態について、SELECT句内で必要なカラムを指定している…という感じです。

「piyo」はuserテーブルに存在せず一致するレコードがないため、返していません。
退会したのでしょうか。

外部結合

しかし例えばmixiのようなものだと、
退会したユーザーのコメントも表示したままだったりします。
それを実現したい場合は「LEFT JOIN」を使用します。

SELECT
  `tweet`.`tweet_id`
 ,`tweet`.`user_id`
 ,`user`.`name`
 ,`tweet`.`text`
FROM
  `tweet`
LEFT JOIN
  `user`
  ON `tweet`.`user_id` = `user`.`user_id`
ORDER BY
  `tweet`.`tweet_id` DESC

「INNER」を「LEFT」に書き換えただけ。
この場合の結果セットは以下の通りです。

tweet_id user_id name text
6 piyo NULL ごはんおいしい
5 fuga ふがふが やっぱむり
4 fuga ふがふが 圧倒的成長
3 hoge ほげほげ 会社いきたくない
2 fuga ふがふが たのしいじんせい
1 hoge ほげほげ そろそろ帰りたい

こちらもtweetテーブルに対してuserテーブルを結合しているのですが、
LEFT JOINとした場合、userテーブル側に該当するものがない場合もNULLとして返却します。
尚、「user_id：piyo」を返してきているのはマズい、という場合は
SELECT句内の「`tweet`.`user_id`」を「`user`.`user_id`」にすればNULLになります。

ちなみに、LEFTがあるからにはRIGHTもあります。
下記クエリは、先ほどの「LEFT」版と同じ結果になります。

SELECT
  `tweet`.`tweet_id`
 ,`tweet`.`user_id`
 ,`user`.`name`
 ,`tweet`.`text`
FROM
  `user`
RIGHT JOIN
  `tweet`
  ON `user`.`user_id` = `tweet`.`user_id`
ORDER BY
  `tweet`.`tweet_id` DESC

個人的にはRIGHTは全く使いません。
INNERとLEFTだけを使っていくことで、
「書いてある順に結合している状態」がイメージしやすいと思っています。

参考文献： MySQL - RIGHT JOINの使い道(38023)｜teratail

そしてさらにもう一つ、FULL JOINというものもあります。
これはLEFTとRIGHTの概念を合わせて、
「両テーブルにあるもの・元のテーブルにのみあるもの・結合したテーブルにのみあるもの」をすべて返します。

…が、MySQLにはそういう構文はないので、詳しい書き方は省略します。

クロス結合

これは、感覚的には内部結合・外部結合とは少し性質が異なるものとなります。
「各テーブルに存在する行同士の全組み合わせ」が得られます。
つまり10レコードしかないテーブル同士を結合すると100行になります。
正直、実務で使ったことはありませんしあまり用途も思い浮かびません。

例えばtable_1とtable_2がそれぞれ以下のような状態とします。

product_id product_name
1 色鉛筆
2 ボールペン
3 絵の具
color_id color_name
1 緑
2 青

この2テーブルに対して以下クエリを実行すると、結果セットは下記の通り。
（全組み合わせなので、ONによる条件指定はありません。
　結合結果に対してWHEREで条件を指定して絞り込むことはできます）

SELECT *
FROM `table_1`
CROSS JOIN `table_2`
product_id product_name color_id color_name
1 色鉛筆 1 緑
1 色鉛筆 2 青
2 ボールペン 1 緑
2 ボールペン 2 青
3 絵の具 1 緑
3 絵の具 2 青

まとめ

  • ONで指定された条件に一致するレコード同士を結合するのが内部結合（INNER JOIN）
  • 基準となるテーブルに存在するなら残したまま結合するのが外部結合
    • 前に書いたテーブルを基準とするならLEFT JOIN
    • 後に書いたテーブルを基準とするならRIGHT JOIN
    • 両方ともを基準とするならFULL JOIN
  • 全ての組み合わせ結合を行うのがクロス結合（CROSS JOIN）

あまりしっくり来ていない人向けに、手作り感満載の画像と参考文献を貼っておきます。

SELECT    *
FROM      `table_1`
○○ JOIN `table_2` ON 条件

の○○の部分をどうするかによる違い（内部結合・外部結合）
f:id:honey8823:20180508164609p:plain:w300

参考文献

結合入門（クロス結合、内部結合、外部結合） - Qiita
SQL素人でも分かるテーブル結合(inner joinとouter join) - Qiita
これでわかった!? LEFT / RIGHT JOIN. - Qiita
【MySQL5.6】完全外部結合(FULL OUTER JOIN)のやりかた - Qiita