Open5
SQLのJOINについて理解する
O.S背景
- 今までO/Rマッパーを用いていたので、何となくJOINを使ってきた。この機会に復習したい。
参考
今回使用するschema
O.S事前準備
mysqlコマンドでログインする
mysqlのPORTを3102にマッピングした場合
$ mysql -h 127.0.0.1 -P 3102 -u root -p
databaseを切り替える
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| app_development |
| app_test |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> use app_development;
Database changed
テーブルに今回使用するレコードを挿入する
companiesテーブルのインサート文
INSERT INTO companies (name, telephone_number, created_at, updated_at) VALUES
('セブン&アイ・ホールディングス', '0120-123-456', NOW(), NOW()),
('ローソン', '0120-456-789', NOW(), NOW()),
('ミニストップ', '0120-789-012', NOW(), NOW()),
('ファミリーマート', '0120-012-345', NOW(), NOW()),
('ユニー・ホールディングス', '0120-345-678', NOW(), NOW()),
('セイコーホールディングス', '0120-678-901', NOW(), NOW()),
('会社G', '0120-901-234', NOW(), NOW()),
('会社H', '0120-234-567', NOW(), NOW()),
('会社I', '0120-567-890', NOW(), NOW()),
('会社J', '0120-890-123', NOW(), NOW());
employeesテーブルのインサート文
INSERT INTO employees (company_id, last_name, first_name, email, password_digest, birthday, created_at, updated_at) VALUES
(1, '鈴木', '一郎', 'ichiro1@example.com', 'password1', '1980-01-01', NOW(), NOW()),
(1, '佐藤', '二郎', 'jiro1@example.com', 'password2', '1981-02-02', NOW(), NOW()),
(1, '田中', '三郎', 'saburo1@example.com', 'password3', '1982-03-03', NOW(), NOW()),
(1, '山田', '四郎', 'shiro1@example.com', 'password4', '1983-04-04', NOW(), NOW()),
(1, '中村', '五郎', 'goro1@example.com', 'password5', '1984-05-05', NOW(), NOW()),
(2, '小林', '六郎', 'rokuro2@example.com', 'password6', '1985-06-06', NOW(), NOW()),
(2, '加藤', '七郎', 'shichiro2@example.com', 'password7', '1986-07-07', NOW(), NOW()),
(2, '吉田', '八郎', 'hachiro2@example.com', 'password8', '1987-08-08', NOW(), NOW()),
(2, '高橋', '九郎', 'kuro2@example.com', 'password9', '1988-09-09', NOW(), NOW()),
(2, '伊藤', '十郎', 'juro2@example.com', 'password10', '1989-10-10', NOW(), NOW()),
(3, '渡辺', '明', 'akira3@example.com', 'password11', '1990-11-11', NOW(), NOW()),
(3, '山本', '哲也', 'tetsuya3@example.com', 'password12', '1991-12-12', NOW(), NOW()),
(3, '中島', '健人', 'kento3@example.com', 'password13', '1992-01-13', NOW(), NOW()),
(3, '山崎', '賢人', 'kento4@example.com', 'password14', '1993-02-14', NOW(), NOW()),
(3, '松本', '涼介', 'ryosuke3@example.com', 'password15', '1994-03-15', NOW(), NOW()),
(4, '井上', '真央', 'mao4@example.com', 'password16', '1995-04-16', NOW(), NOW()),
(4, '木村', '拓哉', 'takuya4@example.com', 'password17', '1996-05-17', NOW(), NOW()),
(4, '草彅', '剛', 'tsuyoshi4@example.com', 'password18', '1997-06-18', NOW(), NOW()),
(4, '香取', '慎吾', 'shingo4@example.com', 'password19', '1998-07-19', NOW(), NOW()),
(4, '稲垣', '吾郎', 'goro4@example.com', 'password20', '1999-08-20', NOW(), NOW()),
(5, '長瀬', '智也', 'tomoya5@example.com', 'password21', '2000-09-21', NOW(), NOW()),
(5, '堂本', '光一', 'koichi5@example.com', 'password22', '2001-10-22', NOW(), NOW()),
(5, '福山', '雅治', 'masaharu5@example.com', 'password23', '2002-11-23', NOW(), NOW()),
(5, '松田', '翔太', 'shota5@example.com', 'password24', '2003-12-24', NOW(), NOW()),
(5, '桐谷', '健太', 'kenta5@example.com', 'password25', '2004-01-25', NOW(), NOW()),
(6, '小泉', '孝太郎', 'kotaro6@example.com', 'password26', '2005-02-26', NOW(), NOW()),
(6, '二宮', '和也', 'kazunari6@example.com', 'password27', '2006-03-27', NOW(), NOW()),
(6, '櫻井', '翔', 'sho6@example.com', 'password28', '2007-04-28', NOW(), NOW()),
(6, '相葉', '雅紀', 'masaki6@example.com', 'password29', '2008-05-29', NOW(), NOW()),
(6, '松本', '潤', 'jun6@example.com', 'password30', '2009-06-30', NOW(), NOW());
テーブル結合における左右
fromに書いたテーブルが左、join句に書いたテーブルが右となる。
以下のSQLの例でいうとcompaniesテーブルが左、employeesが右となる。
select *
from companies
join employees on companies.id = employees.company_id;
各結合
内部結合
被っている部分だけ取得する。
以下の例でいうと、companiesテーブルのidカラムと、employeesテーブルのcompany_idカラムが一致しているレコードのみ結合して取得する。
select companies.id, companies.name, employees.id, employees.company_id, employees.last_name, employees.first_name
from companies
join employees on companies.id = employees.company_id;
左外部結合
from句に指定したテーブルにあるレコードを全て取得した上で結合した結果を取得する。
以下の例でいうと、companiesテーブルのレコードの全てをbaseとして、employeesテーブルの情報を取得している。
この時、companiesテーブルのレコードに対して、該当するemployeesテーブルのレコードが存在しなければ、employeesテーブルのcolumnはnullになる。
select companies.id, companies.name, employees.id, employees.company_id, employees.last_name, employees.first_name
from companies
LEFT JOIN employees on companies.id = employees.company_id;
SQLの結果の例
左外部結合のSQLの結果
+----+-----------------------------------------------------+------+------------+-----------+------------+
| id | name | id | company_id | last_name | first_name |
+----+-----------------------------------------------------+------+------------+-----------+------------+
| 1 | セブン&アイ・ホールディングス | 1 | 1 | 鈴木 | 一郎 |
| 1 | セブン&アイ・ホールディングス | 2 | 1 | 佐藤 | 二郎 |
| 1 | セブン&アイ・ホールディングス | 3 | 1 | 田中 | 三郎 |
| 1 | セブン&アイ・ホールディングス | 4 | 1 | 山田 | 四郎 |
| 1 | セブン&アイ・ホールディングス | 5 | 1 | 中村 | 五郎 |
| 2 | ローソン | 6 | 2 | 小林 | 六郎 |
| 2 | ローソン | 7 | 2 | 加藤 | 七郎 |
| 2 | ローソン | 8 | 2 | 吉田 | 八郎 |
| 2 | ローソン | 9 | 2 | 高橋 | 九郎 |
| 2 | ローソン | 10 | 2 | 伊藤 | 十郎 |
| 3 | ミニストップ | 11 | 3 | 渡辺 | 明 |
| 3 | ミニストップ | 12 | 3 | 山本 | 哲也 |
| 3 | ミニストップ | 13 | 3 | 中島 | 健人 |
| 3 | ミニストップ | 14 | 3 | 山崎 | 賢人 |
| 3 | ミニストップ | 15 | 3 | 松本 | 涼介 |
| 4 | ファミリーマート | 16 | 4 | 井上 | 真央 |
| 4 | ファミリーマート | 17 | 4 | 木村 | 拓哉 |
| 4 | ファミリーマート | 18 | 4 | 草彅 | 剛 |
| 4 | ファミリーマート | 19 | 4 | 香取 | 慎吾 |
| 4 | ファミリーマート | 20 | 4 | 稲垣 | 吾郎 |
| 5 | ユニー・ホールディングス | 21 | 5 | 長瀬 | 智也 |
| 5 | ユニー・ホールディングス | 22 | 5 | 堂本 | 光一 |
| 5 | ユニー・ホールディングス | 23 | 5 | 福山 | 雅治 |
| 5 | ユニー・ホールディングス | 24 | 5 | 松田 | 翔太 |
| 5 | ユニー・ホールディングス | 25 | 5 | 桐谷 | 健太 |
| 6 | セイコーホールディングス | 26 | 6 | 小泉 | 孝太郎 |
| 6 | セイコーホールディングス | 27 | 6 | 二宮 | 和也 |
| 6 | セイコーホールディングス | 28 | 6 | 櫻井 | 翔 |
| 6 | セイコーホールディングス | 29 | 6 | 相葉 | 雅紀 |
| 6 | セイコーホールディングス | 30 | 6 | 松本 | 潤 |
| 7 | 会社G | NULL | NULL | NULL | NULL |
| 8 | 会社H | NULL | NULL | NULL | NULL |
| 9 | 会社I | NULL | NULL | NULL | NULL |
| 10 | 会社J | NULL | NULL | NULL | NULL |
+----+-----------------------------------------------------+------+------------+-----------+------------+
右外部結合
JOIN句に指定したテーブルにあるレコードを全て取得した上で結合した結果を取得する。
以下の例でいうと、employeesテーブルのレコードの全てをbaseとして、companiesテーブルの情報を取得している。
この時、employeesテーブルのレコードに対して、該当するcompaniesテーブルのレコードが存在しなければ、companiesテーブルのcolumnはnullになる。
（今回の例だと、companiesは親テーブルなので、それはありえないが、、、、）
select companies.id, companies.name, employees.id, employees.company_id, employees.last_name, employees.first_name
from companies
RIGHT JOIN employees on companies.id = employees.company_id;
ちなみに、以下の例だと、左外部結合の例で出したSQLと結果が同じになる。
companiesテーブルのレコードに対して、該当するemployeesテーブルのレコードが存在しなければ、employeesテーブルのcolumnはnullになる。
select companies.id, companies.name, employees.id, employees.company_id, employees.last_name, employees.first_name
from employees
RIGHT JOIN companies on companies.id = employees.company_id;
SQLの結果の例
右外部結合のSQLの結果の例
+------+---------------------------------------------+----+------------+-----------+------------+
| id | name | id | company_id | last_name | first_name |
+------+---------------------------------------------+----+------------+-----------+------------+
| 1 | セブン&アイ・ホールディングス | 1 | 1 | 鈴木 | 一郎 |
| 1 | セブン&アイ・ホールディングス | 2 | 1 | 佐藤 | 二郎 |
| 1 | セブン&アイ・ホールディングス | 3 | 1 | 田中 | 三郎 |
| 1 | セブン&アイ・ホールディングス | 4 | 1 | 山田 | 四郎 |
| 1 | セブン&アイ・ホールディングス | 5 | 1 | 中村 | 五郎 |
| 2 | ローソン | 6 | 2 | 小林 | 六郎 |
| 2 | ローソン | 7 | 2 | 加藤 | 七郎 |
| 2 | ローソン | 8 | 2 | 吉田 | 八郎 |
| 2 | ローソン | 9 | 2 | 高橋 | 九郎 |
| 2 | ローソン | 10 | 2 | 伊藤 | 十郎 |
| 3 | ミニストップ | 11 | 3 | 渡辺 | 明 |
| 3 | ミニストップ | 12 | 3 | 山本 | 哲也 |
| 3 | ミニストップ | 13 | 3 | 中島 | 健人 |
| 3 | ミニストップ | 14 | 3 | 山崎 | 賢人 |
| 3 | ミニストップ | 15 | 3 | 松本 | 涼介 |
| 4 | ファミリーマート | 16 | 4 | 井上 | 真央 |
| 4 | ファミリーマート | 17 | 4 | 木村 | 拓哉 |
| 4 | ファミリーマート | 18 | 4 | 草彅 | 剛 |
| 4 | ファミリーマート | 19 | 4 | 香取 | 慎吾 |
| 4 | ファミリーマート | 20 | 4 | 稲垣 | 吾郎 |
| 5 | ユニー・ホールディングス | 21 | 5 | 長瀬 | 智也 |
| 5 | ユニー・ホールディングス | 22 | 5 | 堂本 | 光一 |
| 5 | ユニー・ホールディングス | 23 | 5 | 福山 | 雅治 |
| 5 | ユニー・ホールディングス | 24 | 5 | 松田 | 翔太 |
| 5 | ユニー・ホールディングス | 25 | 5 | 桐谷 | 健太 |
| 6 | セイコーホールディングス | 26 | 6 | 小泉 | 孝太郎 |
| 6 | セイコーホールディングス | 27 | 6 | 二宮 | 和也 |
| 6 | セイコーホールディングス | 28 | 6 | 櫻井 | 翔 |
| 6 | セイコーホールディングス | 29 | 6 | 相葉 | 雅紀 |
| 6 | セイコーホールディングス | 30 | 6 | 松本 | 潤 |
+------+---------------------------------------------+----+------------+-----------+------------+
30 rows in set (0.05 sec)
これ、内部結合と左外部結合の説明と結果が間違ってるな
この例だと内部結合と左外部結合の結果は同じになるはず
O.S応用編
外部結合におけるnullを省く
よくあるケースな気がする。
以下のSQLでは、where句がない状態だとemployeesのカラムがnullの状態のものが出てきてしまうため、
employeesのカラムがnullでないものだけ取得するようにしている。
select companies.id, companies.name, employees.id, employees.company_id, employees.last_name, employees.first_name
from employees
RIGHT JOIN companies on companies.id = employees.company_id
where employees.company_id is not null;
完全外部結合
Draft
(MySQLでは、UNIONを使用する必要がある。)