アナログCPU:5108843109

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

('ω') < 転職した

ざっくりSQL入門 #5:クエリを書く<テーブル結合編>

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