2020年4月のDBスペシャリストを受験することにしました。
データベーススペシャリスト試験(DB) ～ ビッグデータ時代に求められる、データ志向の担い手 ～
ということで今回は内部結合・外部結合の違いについてまとめていこうと思います。
結合とは
結合とは、色々な種類があるのですが、テーブルを繋げてデータを取得する方法の１つです。
ユーザが触れる画面表示などの処理では基本的には使わないようにと注意されるものでもあります。それはなぜかというと、処理が重いからだったり、他の処理の妨げになったりするからです。
以前働いていたオンラインゲーム業界では、JOINは完全にご法度でした。JOINではなく１つ１つのテーブルを走査するクエリを書けと言われていました。
そういった理由でこれまであまり仕事で使わないものだったので、その分勉強もおろそかになっていた部分でもあります。
しかし、データベーススペシャリスト試験の過去問をやっていると結構出てくるものなので、せっかくだからしっかり学んでみようと思い、本記事を書くことにしました。本記事では結合の種類についてそれぞれまとめていきたいと思います。
サンプルデータ
テーブルを用意
さて、まずはサンプルのテーブルを作っていきます。
よくあるやつですが、社員テーブルと部門テーブルを作成します。社員テーブルは部門テーブルのPK（プライマリキー）を外部キーとして使用しているので、部門テーブルから作っていきます
# 部門テーブル
CREATE TABLE IF NOT EXISTS departments (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '部門ID',
name varchar(64) NOT NULL COMMENT '部門名'
)
DEFAULT CHARSET=utf8
COMMENT '部門テーブル';
# 社員テーブル
CREATE TABLE IF NOT EXISTS siken.employees (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '社員ID',
name varchar(64) NOT NULL COMMENT '名前',
gender tinyint NOT NULL COMMENT '性別(1:男,2:女)',
dept_id int NOT NULL COMMENT '部門ID',
joined_at date COMMENT '入社年月日',
birthdate date COMMENT '生年月日',
FOREIGN KEY fk_dept_id(dept_id) REFERENCES departments(id)
)
DEFAULT CHARSET=utf8
COMMENT '社員テーブル';データ作成
適当にデータを作っていきます。前述したように、まずは部門から作っていきます。
INSERT INTO departments (id, name)
VALUES
(null, "製造"),
(null, "営業"),
(null, "企画"),
(null, "人事"),
(null, "経理");SELECTするとこんな感じです。
1 製造
2 営業
3 企画
4 人事
5 経理次に社員データを作成します。
INSERT INTO employees (id, name, gender, dept_id, joined_at, birthdate)
VALUES
(null, "岡村", 1, 1, "2020-01-01", "1970-07-03"),
(null, "矢部", 1, 1, "2010-03-04", "1971-10-23"),
(null, "粗品", 1, 2, "2012-10-15", "1993-01-07"),
(null, "せいや", 1, 2, "2014-02-03", "1992-09-13"),
(null, "誠子", 2, 2, "2003-04-02", "1988-12-04"),
(null, "渚", 2, 2, "2004-06-25", "1984-08-06"),
(null, "加藤", 1, 3, "2015-05-30", "1969-04-26"),
(null, "山本", 1, 4, "2019-12-01", "1968-02-23");SELECTするとこんな感じです。
1 岡村 1 1 2020-01-01 1970-07-03
2 矢部 1 1 2010-03-04 1971-10-23
3 粗品 1 2 2012-10-15 1993-01-07
4 せいや 1 2 2014-02-03 1992-09-13
5 誠子 2 2 2003-04-02 1988-12-04
6 渚 2 2 2004-06-25 1984-08-06
7 加藤 1 3 2015-05-30 1969-04-26
8 山本 1 4 2019-12-01 1968-02-23内部結合と外部結合の違い
内部結合 (INNER JOIN)
内部結合とは、２つのテーブルの結合の対象のカラムを指定することにより、各々のカラムが合致するデータを結合するものです。合致するデータのみですので、合致しないデータに至っては検索結果に入りません。
今回作成したテーブルは、外部キーを使っているので必ず存在するものとなっています。
ちなみに、JOINとINNER JOINは同義です。ここではわかりやすいように明示的にINNERを記述しています。
例えば、社員テーブルと部門テーブルでは部門IDで結合することができます。
この場合、結合の対象のカラムは部門IDと言えます。
百聞は一見に如かず。実際にやってみましょう。下記のクエリを実行してみます。
SELECT
e.name AS '社員名', d.name AS '部門名'
FROM
employees AS e
INNER JOIN
departments AS d
ON
e.dept_id = d.id;結果：
岡村 製造
矢部 製造
粗品 営業
せいや 営業
誠子 営業
渚 営業
加藤 企画
山本 人事外部結合（OUTER JOIN）
次に外部結合です。
内部結合が、条件に合致するものしか検索結果に出さないのに対して外部結合では条件に合致しないものでも結果として表示させてくれます。
ちなみに、外部結合のクエリは以下のルールとなっています
- LEFT JOIN = LEFT OUTER JOIN
- RIGHT JOIN = RIGHT OUTER JOIN
内部結合の記述ルールでは JOIN = INNER JOIN となっているので、 LEFT JOIN = LEFT INNER JOINになるかと思いきや、実は違います。ちょっとややこしいですね。
ではやってみましょう。
SELECT
e.name AS '社員名', d.name AS '部門名'
FROM
employees AS e
RIGHT JOIN
departments AS d
ON
e.dept_id = d.id;結果
岡村 製造
矢部 製造
粗品 営業
せいや 営業
誠子 営業
渚 営業
加藤 企画
山本 人事
NULL 経理条件に合致しないものも結果として表示されました。
RIGHTとかLEFTって何？
RIGHTとLEFTを理解するのは右のテーブル、左のテーブルという言葉を理解する必要があります。
よく、右のテーブル、左のテーブルと表現されますが、これってなんなのでしょうか？
- 右のテーブルはJOINで指定されているテーブル
- 左のテーブルはFROMで指定されているテーブル
JOINにRIGHTを指定すると、JOINで指定されているテーブルを元に結合処理が実行されます。
逆に、JOINにLEFTを指定すると、FROMで指定されているテーブルを元に結合されます。
上のクエリで RIGHT JOINの結果に NULLが入っていますね。RIGHTなので結合の元はJOINで指定されている部門テーブルです。
部門テーブルの経理に合致するデータが社員テーブルにないけど取得したよ、NULLだよ。という結果になります。
まとめ
まとめるとこんな感じになります
内部結合：両方のテーブルに存在し結合カラムが合致するデータを取得する（合致しないものは取得しない）
外部結合：結合カラムが合致するしないに関わらず取得する
右のテーブル：JOINで指定されているテーブル
左のテーブル：FROMで指定されているテーブル
JOIN = INNER JOIN
LEFT JOIN = LEFT OUTER JOIN
RIGHT JOIN = RIGHT OUTER JOIN
データベーススペシャリスト試験では様々な形で結合の問題が出されます。この記事の学習内容が少しでもお役に立てたらなと思います。
最後までお読みいただきありがとうございました。
