Help us understand the problem. What is going on with this article?

これでわかった!? 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には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は、等価結合と同じです(厳密には違いますが)。

zaburo
こんにちは。自分用のメモをだらだら公開しています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした