Edited at

Railsエンジニアなら最低限これだけは知っておきたいSQLのJOINの動き

More than 1 year has passed since last update.


はじめに

先日、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の種類

ここでは3つのJOINの説明をします。

FULL JOIN等まだ他にも種類はありますが、基本的な3つを押さえていきます。

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(内部結合)

まず大きく分けて2つのJOINがあります。

すごいざっくり説明すると以下のような違いがあります。

INNER JOIN:

結合条件に一致した行が残ります。
OUTER JOIN:
結合条件に一致しない行も残ります。

むー、よくわからないですね。。

具体的に動きをみていきましょう。


それぞれのJOINの動きをみてみよう


INNER JOIN (LEFT INNER JOIN)

FROMで指定するテーブル(左テーブル)と他のテーブル(右テーブル)を外部キーをキーに結合します。

左テーブルにある行に対して、1行づつ右テーブルの行をみていき、結合します。もし、右テーブル内にマッチする外部キーが複数あれば、左テーブルの行は自動的にコピーされます。

一方で、右のテーブルにマッチする行が1つもなければ、左のテーブルのその行は削除されます。したがって、元々の左テーブルの行数より、結合後の行は減る場合もあれば、増える場合もあります。

SQLクエリ


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を1つも持っていないので削除される。

+----+--------+-----+---------+----------------------+-----------+
| 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で指定するテーブル(左テーブル)と他のテーブル(右テーブル)を外部キーをキーに結合します。

左テーブルにある行に対して、1行づつ右テーブルの行をみていき、結合します。もし、右テーブル内にマッチする外部キーが複数あれば、左テーブルの行は自動的にコピーされます。

また、左テーブルの行は必ず取り出されます。したがって、元々の左テーブルの行数より、結合後の行は増える場合があります。空白のカラムはNULLになります。

SQLクエリ


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を1つも持っていないので、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で指定するテーブル(左テーブル)と他のテーブル(右テーブル)を外部キーをキーに結合します。

右テーブルにある行に対して、1行づつ左テーブルの行をみていき、結合します。もし、左テーブル内にマッチする外部キーが複数あれば、右テーブルの行は自動的にコピーされます。

また、右テーブルの行は必ず取り出されます。したがって、元々の右テーブルの行数より、結合後の行は増える場合があります。空白のカラムはNULLになります。

SQLクエリ


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を1つも持っていないので、削除されている。

+----+--------+-----+---------+----------------------+-----------+
| 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/