これでわかった!? LEFT / RIGHT JOIN.

JOINを曖昧に使っている人に説明するためのメモ。
LEFT, RIGHTやOUTER, INNERの違いを整理。

環境

• 検証はMySQLで行っています。

テストDBおよびテーブルの用意

ここでは、正規化された社員管理用に部署テーブル(depts)と社員テーブル(employees)を作成し、各種JOIN句を実行して期待の結果が得られるようにしてみます。

データベースの作成

もちろん既存のDBでも構いません。

create database testdb;
use testdb;

部署テーブル(depts)の作成

部署コードと部署名のシンプルなテーブル。

drop table if exists depts;
create table depts(
	dept_id int primary key,
	dept_name varchar(32)
);

insert into depts(dept_id,dept_name) values(1,'営業部');
insert into depts(dept_id,dept_name) values(2,'経理部');
insert into depts(dept_id,dept_name) values(3,'技術部');
insert into depts(dept_id,dept_name) values(4,'法務部');

従業員テーブル(employees)の作成

社員ID,社員名,部署コードから構成される。

#employees
drop table if exists employees;
create table employees(
	id int primary key auto_increment,
	dept_id int,
	name varchar(32)
);

insert into employees(dept_id,name) values(1,'田中');
insert into employees(dept_id,name) values(2,'玉木');
insert into employees(dept_id,name) values(3,'鈴木');
insert into employees(dept_id,name) values(3,'山本');
insert into employees(dept_id,name) values(2,'斉藤');
insert into employees(dept_id,name) values(1,'佐藤');
insert into employees(dept_id,name) values(1,'小澤');
insert into employees(dept_id,name) values(2,'関野');
insert into employees(dept_id,name) values(0,'中村');

テーブル内容の確認(depts)

4つの部署と部署コードが登録されています。

mysql> select * from depts;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 営業部     |
|       2 | 経理部     |
|       3 | 技術部     |
|       4 | 法務部     |
+---------+-----------+

テーブル内容の確認(employees)

データの要点

employeesテーブルを見て下さい。
いくつか注意、記憶しておいてほしいポイントがあります。

• 法務部に所属する人間(dept_id=4)が１人もいない。
• 中村さんは、どの部署にも所属していない(dept_id=0)。

この2点だけしっかり覚えておいて下さい。

mysql> select * from employees;
+----+---------+--------+
| id | dept_id | name   |
+----+---------+--------+
|  1 |       1 | 田中   |
|  2 |       2 | 玉木   |
|  3 |       3 | 鈴木   |
|  4 |       3 | 山本   |
|  5 |       2 | 斉藤   |
|  6 |       1 | 佐藤   |
|  7 |       1 | 小澤   |
|  8 |       2 | 関野   |
|  9 |       0 | 中村   |
+----+---------+--------+

結合してみる

上記のようなテーブルがあるときに、一番ニーズのあるクエリは、dept_idでは、どこの部署か直感的にわからないため、日本語で部署名を表示させたいというものです。

このような効果を得るために通常「結合(JOIN)」という操作を行いますが、使用するJOIN句により、得られる結果が異なるため、それをおさらいしてみます。

では、それぞれ、パターンと結果を見ていきます。

等価結合

よく使われる結合です。WHERE文に結合のkeyとなるカラムを指定します。

select
	depts.dept_name,
	employees.name
from
	depts,employees
where depts.dept_id = employees.dept_id;

なお、私は、クエリを小文字で書く習慣がありますので、全て小文字で書いていますが、SQL分はSELECT FROM WHEREと大文字で書く方がわかりやすいかもしれません。もちろん結果に差はありません。

実行結果は下記の通りです。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
+-----------+--------+

アプリ開発の多くの場合で、これで事足りますが、いくつか注意点があります。

• 法務部が表示されていません。
• 中村さんが表示されていません。

お気づきの通り、どちらかのテーブルに存在しないものは表示されていません（逆に言えば、両方のテーブルに存在するものが表示されています）。

このように等価結合というクエリでは、keyで指定したデータで、互いのテーブルに存在するものが結合、表示されます。

一方、

• 所属の人がいるかいないかに関わらず、部署は表示したい。
• 所属部署が決まっていない人も表示したい。

などの要求がある場合は、等価結合では情報不足です。

その場合は、LEFT (OUTER) JOIN, RIGHT (OUTER) JOINなどを利用して、期待する結果を得られるようにします。

LEFT JOIN

部署を全て表示してみる

詳細の説明に入る前に、ひとまず、以下のクエリを実行してみてください。

select
	depts.dept_name,
	employees.name
from depts left join employees
on depts.dept_id = employees.dept_id;

誰も所属していない法務部が表示されました。

但し、無所属の中村さんは表示されていません。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 法務部     | NULL   |
+-----------+--------+

詳細を説明する前に、さらにRIGHT JOINを実行してみましょう。

RIGHT JOIN

上記のクエリの文はそのままで、leftの部分をrightに変えて実行してみて下さい。

select
	depts.dept_name,
	employees.name
from depts right join employees
on depts.dept_id = employees.dept_id;

今度は、中村さんが表示されました。

が、法務部は表示されていません。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 経理部     | 玉木   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 経理部     | 斉藤   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 関野   |
| NULL      | 中村   |
+-----------+--------+

結局LEFT JOINとRIGHT JOINって何？

とりあえず、left joinとright joinを実行してみました。
何となく違いはわかりましたが、ここでまとめて見ましょう。

OUTER JOIN

まず、LEFT JOIN, RIGHT JOINは、LEFT OUTER JOIN, RIGHT OUTER JOINの省略系です。
LEFTであろうと、RIGHTであろうと、OUTER JOINという属性のクエリです。

OUTER JOINとは、複数のテーブルがあり、それを結合する際、優先テーブルを１つ決め、そこにある情報は全て表示しながら、他のテーブル情報に対になるデータがあれば表示する（ない場合はNULLで補完表示）。

という機能となります。そして、LEFT, RIGHTは、そのテーブルが優先テーブルなのかを決めるために使っています。

ここでは優先テーブルという名前を使っていますが、マスターテーブルとかいろいろな呼び名があります。

#何を基準に左(LEFT)、右(RIGHT)なの？

右、左は、下記の図の通り、FROM句の中での位置関係です。

優先テーブルに指定されると、そのテーブルの項目は漏れ無く表示される

ということです。なので、

• from depts left join employees
• from employees right join depts

は等価になります（どちらも、優先テーブルはdepts）。

実際に実行してみましょう。
下記は、先ほど紹介したRIGHT JOIN句はそのままにテーブルの位置を入れ替えたものです。

select
	depts.dept_name,
	employees.name
from employees right join depts
on depts.dept_id = employees.dept_id;

結果は、from depts left join employeesとした場合と同じです。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 法務部     | NULL   |
+-----------+--------+

全ての情報を結合したい

では、両方に無いけど、全てのレコードを結合したい場合はどうすればいいでしょうか？
これはDBにより異なりますが、ここでは、UNIONを使って、left joinとright joinの2つのクエリを結合してみます。

select
	depts.dept_name,
	employees.name
from depts left join employees
on depts.dept_id = employees.dept_id

union

select
	depts.dept_name,
	employees.name
from depts right join employees
on depts.dept_id = employees.dept_id;

UNIONは2つのクエリを結合するものです（重複文は表示されません）。
法務部も中村さんも表示されています。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 法務部     | NULL   |
| NULL      | 中村   |
+-----------+--------+

INNER JOIN

では、続いてinner joinを見てみます。

INNER JOINにはLEFT, RIGHTの概念は無い（たぶん）

inner joinは、その名の通り、outerjoinの逆なので、「どちらのテーブルにあるものを結合し表示する」ということになります。なので、結合するテーブルのどちらを優先テーブルに指定しようが、どちらにもあるものしか表示されないので、そもそも優先テーブルを指定する必要や意味がありません。

文法的にはFROMの直後にくる（LEFT）のものが優先テーブル的なものにはなります。

ひとまず、下記のクエリを実行してみます。

select
	depts.dept_name,
	employees.name
from depts inner join employees
on depts.dept_id = employees.dept_id;

このような結果が得られました。法務部も中村さんも表示されていません。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部  　  | 田中   |
| 営業部  　  | 佐藤   |
| 営業部   　 | 小澤   |
| 経理部   　 | 玉木   |
| 経理部  　  | 斉藤   |
| 経理部  　  | 関野   |
| 技術部  　  | 鈴木   |
| 技術部  　  | 山本   |
+-----------+--------+

テーブルの書く位置を変えてみる

先ほど補足したとおり、inner joinにも優先テーブルという概念が無いわけではありません。fromの直後に来るテーブルが文法上の優先テーブルです。念のため、deptsとemployeesを入替えて実行してみてください。

select
	depts.dept_name,
	employees.name
from employees
inner join depts on depts.dept_id = employees.dept_id;

やはり結果は同じようです。

+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
+-----------+--------+

さらに、この結果、どこかで見ました。最初に試した等価結合です。
inner joinは、等価結合と同じです（厳密には違いますが）。