これでわかった!? 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)が1人もいない。

  • 中村さんは、どの部署にも所属していない(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とは、複数のテーブルがあり、それを結合する際、優先テーブルを1つ決め、そこにある情報は全て表示しながら、他のテーブル情報に対になるデータがあれば表示する(ない場合はNULLで補完表示)。

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


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



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

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

join


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

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


  • 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にはLIGHT, 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は、等価結合と同じです(厳密には違いますが)。