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は、等価結合と同じです（厳密には違いますが）。