はじめに
先日、ActiveRecordでテーブル結合(joins)する際の動作の違いを実際に調べましたが、SQLのJOIN句の詳しい説明はできませんでしたので、今回はJOIN句の種類と動きを整理してみます。
Railsを初めて間もないですが、これだけは知っておいたほうが(・∀・)ｲｲ!!というところだけまとめてみました。
これは間違っているよ！という指摘があれば、コメント頂けると幸いです。
以前書いた記事
ActiveRecordのincludes、joins、eager_load等の違いを調べてみた
環境
環境は以下のとおりです。
Ruby 2.3.1p112 (2016-04-26 revision 54768)
ActiveRecord 5.0.1
MySQL 5.6.34
モデルやテーブル情報など
動作確認に使うモデル・テーブルのリレーションとデータは以下のとおりです。
class User < ActiveRecord::Base
has_many :items
end
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | user01 | 10 |
| 2 | user02 | 20 |
| 3 | user03 | 30 |
+----+--------+-----+
class Items < ActiveRecord::Base
belongs_to :user
end
+----+---------+----------------------+-----------+
| id | user_id | content | published |
+----+---------+----------------------+-----------+
| 1 | 1 | user01_true_text | true |
| 2 | 2 | user02_01_true_text | true |
| 3 | 2 | user02_02_true_text | true |
| 4 | 2 | user02_03_false_text | |
+----+---------+----------------------+-----------+
そもそも結合(JOIN)って？
外部キーを用いてリレーション関係があるテーブル同士をくっ付けることを結合（JOIN）と言います。
外部キーをキーとして、指定した方法に従って結合します。
結合方法はいくつもあり、欲しいデータややりたいことに応じて、適切な結合を選択する必要があります。
JOINの種類
ここでは３つのJOINの説明をします。
FULL JOIN等まだ他にも種類はありますが、基本的な３つを押さえていきます。
JOIN
- INNER JOIN
1)LEFT INNER JOIN
- OUTER JOIN
2)LEFT OUTER JOIN（単にLEFT JOINの場合はコレを指す）
3)RIGHT OUTER JOIN（単にRIGHT JOINの場合はコレを指す）
OUTER JOIN（外部結合） と INNER JOIN（内部結合）
まず大きく分けて２つのJOINがあります。
すごいざっくり説明すると以下のような違いがあります。
INNER JOIN：
結合条件に一致した行が残ります。
OUTER JOIN：
結合条件に一致しない行も残ります。
むー、よくわからないですね。。
具体的に動きをみていきましょう。
それぞれのJOINの動きをみてみよう
INNER JOIN (LEFT INNER JOIN)
FROMで指定するテーブル(左テーブル)と他のテーブル（右テーブル）を外部キーをキーに結合します。
左テーブルにある行に対して、１行づつ右テーブルの行をみていき、結合します。もし、右テーブル内にマッチする外部キーが複数あれば、左テーブルの行は自動的にコピーされます。
一方で、右のテーブルにマッチする行が１つもなければ、左のテーブルのその行は削除されます。したがって、元々の左テーブルの行数より、結合後の行は減る場合もあれば、増える場合もあります。
SQLクエリ
SELECT users.id, users.name, users.age, items.user_id, items.content, items.published FROM `users` INNER JOIN items ON users.id = items.user_id
取り出されたデータ
user02の行は増えているが、user03はitemを１つも持っていないので削除される。
+----+--------+-----+---------+----------------------+-----------+
| id | name | age | user_id | content | published |
+----+--------+-----+---------+----------------------+-----------+
| 1 | user01 | 10 | 1 | user01_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_01_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_02_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_03_false_text | 0 |
+----+--------+-----+---------+----------------------+-----------+
LEFT OUTER JOIN
FROMで指定するテーブル(左テーブル)と他のテーブル（右テーブル）を外部キーをキーに結合します。
左テーブルにある行に対して、１行づつ右テーブルの行をみていき、結合します。もし、右テーブル内にマッチする外部キーが複数あれば、左テーブルの行は自動的にコピーされます。
また、左テーブルの行は必ず取り出されます。したがって、元々の左テーブルの行数より、結合後の行は増える場合があります。空白のカラムはNULLになります。
SQLクエリ
SELECT users.id, users.name, users.age, items.user_id, items.content, items.published FROM `users` LEFT OUTER JOIN items ON users.id = items.user_id
取り出されたデータ
usersテーブルの行は全て表示されている。user03はitemを１つも持っていないので、NULLになる。
+----+--------+-----+---------+----------------------+-----------+
| id | name | age | user_id | content | published |
+----+--------+-----+---------+----------------------+-----------+
| 1 | user01 | 10 | 1 | user01_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_01_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_02_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_03_false_text | 0 |
| 3 | user03 | 30 | | | |
+----+--------+-----+---------+----------------------+-----------+
RIGHT OUTER JOIN
FROMで指定するテーブル(左テーブル)と他のテーブル（右テーブル）を外部キーをキーに結合します。
右テーブルにある行に対して、１行づつ左テーブルの行をみていき、結合します。もし、左テーブル内にマッチする外部キーが複数あれば、右テーブルの行は自動的にコピーされます。
また、右テーブルの行は必ず取り出されます。したがって、元々の右テーブルの行数より、結合後の行は増える場合があります。空白のカラムはNULLになります。
SQLクエリ
SELECT users.id, users.name, users.age, items.user_id, items.content, items.published FROM `users` RIGHT OUTER JOIN items ON users.id = items.user_id
取り出されたデータ
itemsテーブルの行は全て表示されている。user03はitemを１つも持っていないので、削除されている。
+----+--------+-----+---------+----------------------+-----------+
| id | name | age | user_id | content | published |
+----+--------+-----+---------+----------------------+-----------+
| 1 | user01 | 10 | 1 | user01_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_01_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_02_true_text | 1 |
| 2 | user02 | 20 | 2 | user02_03_false_text | 0 |
+----+--------+-----+---------+----------------------+-----------+
終わりに
今回、DBMSとしてMySQLを使いましたが、実は最初Sqlite3を使っていました。しかし、Right outer joinが出来ないなど、制約があったので途中からMySQLに切り替えました。
このようにDBMSごとの違いも結構あります。
やっぱり実際に触ってみないと覚えませんので、改めて手を動かすことの重要性を実感しました。次はINDEXについてまとめたいと思います。
参考にした記事や本
SQLの概要を理解するために以下の本を読みました。分かりやすかったです。
スッキリわかるSQL入門 ドリル215問付き！ スッキリわかるシリーズ
https://www.amazon.co.jp/dp/B00IRRTFNG/