内部結合と外部結合[SQL]
前提
結合とは
結合とは、複数のテーブルを特定のキーで結びつけて処理することです。
DBを使ったシステムやアプリケーションの多くは、1つのテーブルだけで実装されていることは少なく、
何枚かのテーブルを使って成り立っていることが多いです。
ECサイトを例にすると、顧客情報のテーブルと商品（サービス）のテーブル、と少なくとも２つはいります。この２つのテーブルをまとめてしまうことがどれほど厄介で危険なことかは、なんとなく察しがつくはずです。
例で使うテーブル
従業員（employees）が、なに（items）をどれだけ売ったか（sales）というデータがあると想定します。
| employee_id | name |
|---|---|
| 1 | 青木 |
| 2 | 岩野 |
| 3 | 武内 |
| 4 | 中津 |
| 5 | 山口 |
| sale_id | employee_id | item_id | quantity |
|---|---|---|---|
| 1 | 2 | 1 | 8 |
| 2 | 3 | 4 | 12 |
| 3 | 1 | 4 | 6 |
| 4 | 4 | 4 | 10 |
| 5 | 4 | 2 | 2 |
| 6 | 3 | 3 | 5 |
| 7 | 17 | 1 | 5 |
| item_id | price |
|---|---|
| 1 | 1200 |
| 2 | 1250 |
| 3 | 1370 |
| 4 | 980 |
実際に手を使って動かしてみたい方は次のCREATE文を参考に。
3枚のテーブルの最初のIDは面倒だったのでオートインクリメントを使っています。
itemsテーブルにname（商品名）が入ってないので違和感がある方は好きにデータを挿入して
ください。（今回は特に意味を持たないので省きました）
CREATE TABLE employees ( employee_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sales ( sale_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, item_id INT NOT NULL, quantity INT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE items ( item_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, price INT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
内部結語
employeesテーブルとsalesテーブルを内部結合させてみたいと思います。
内部結合を行うにはJOINかINNER JOINのいずれかを使います。
「結合とは」の項目で、結合っていうのは複数のテーブルを特定のキーで結びつけて処理すること
と紹介しました。
この特定のキーをここではemployee_idにしましょう。このキーは両方の
テーブルにありますね。結合をするときは2つのテーブル、どちらにも定義してあるキーを主軸にします。
キーを結びつけるにはON テーブル１.キー = テーブル２.キーを使うかUSING(キー)を
使います。今回はUSINGで統一します。
SELECT * FROM employees INNER JOIN sales USING(employee_id);
結果はこのようになります。
+-------------+--------+---------+---------+----------+ | employee_id | name | sale_id | item_id | quantity | +-------------+--------+---------+---------+----------+ | 2 | 岩野 | 1 | 1 | 8 | | 3 | 武内 | 2 | 4 | 12 | | 1 | 青木 | 3 | 4 | 6 | | 4 | 中津 | 4 | 4 | 10 | | 4 | 中津 | 5 | 2 | 2 | | 3 | 武内 | 6 | 3 | 5 | +-------------+--------+---------+---------+----------+
ここで重要なのが内部結合はキーが一致しているレコードのみを抽出することです。
上の結果では、結合したテーブルではemployeesテーブルのID5山口さんと、salesテーブルのID7の
レコードが抜け落ちています。（どちらも最終のレコード）
山口さんは途中入社でまだ売上の情報がなく、salesテーブルのID7の情報は存在しない従業員ID17を指している
ために表示されません。なにかしらの不手際で紛れ込んだと考えるのが妥当でしょう。
しかし、山口さんの情報も表示したい、というケースもあるはずです。そのような場合は外部結合を使いましょう。
外部結合
外部結合では、どちらのテーブルのレコードをすべて表示するかによってSQLを変える必要があります。
- 左のテーブルの場合はLEFT JOINもしくはLEFT OUTER JOIN
- 右のテーブルの場合はRIGHT JOINもしくはRIGHT OUTER JOIN
を使います。
山口さんの情報を抜き出したい場合、employeesテーブル寄りにするSQLを書けば良いです。
SELECT * FROM employees LEFT OUTER JOIN sales USING(employee_id);
SELECT * FROM sales RIGHT OUTER JOIN employees USING(employee_id);
2つのSQLの結果は同じになります。
+-------------+--------+---------+---------+----------+ | employee_id | name | sale_id | item_id | quantity | +-------------+--------+---------+---------+----------+ | 2 | 岩野 | 1 | 1 | 8 | | 3 | 武内 | 2 | 4 | 12 | | 1 | 青木 | 3 | 4 | 6 | | 4 | 中津 | 4 | 4 | 10 | | 4 | 中津 | 5 | 2 | 2 | | 3 | 武内 | 6 | 3 | 5 | | 5 | 山口 | NULL | NULL | NULL | +-------------+--------+---------+---------+----------+
せっかくなのでsalesテーブル寄りの情報も出力してみます。
SELECT * FROM sales LEFT OUTER JOIN employees USING(employee_id);
SELECT * FROM employees RIGHT OUTER JOIN sales USING(employee_id);
+-------------+---------+---------+----------+--------+ | employee_id | sale_id | item_id | quantity | name | +-------------+---------+---------+----------+--------+ | 1 | 3 | 4 | 6 | 青木 | | 2 | 1 | 1 | 8 | 岩野 | | 3 | 2 | 4 | 12 | 武内 | | 3 | 6 | 3 | 5 | 武内 | | 4 | 4 | 4 | 10 | 中津 | | 4 | 5 | 2 | 2 | 中津 | | 17 | 7 | 1 | 5 | NULL | +-------------+---------+---------+----------+--------+
きちんとsalesテーブルにあるID7のレコードが取れています。employeesテーブルにID17の情報がないので
名前はNULLになっていますね。
応用
- Q. employeesテーブルに登録してある従業員の合計売上を表示せよ
最終的に、従業員名と売上の2つを表示させて、問題に答えたこととします。
その前に、3つの表を結合させて全体像を知っておきましょう。
JOINを3つつなげて書くことで3枚のテーブルの結合を実現できます。
SELECT * FROM employees LEFT JOIN sales USING (employee_id) LEFT JOIN items USING (item_id);
+---------+-------------+--------+---------+----------+-------+ | item_id | employee_id | name | sale_id | quantity | price | +---------+-------------+--------+---------+----------+-------+ | 1 | 2 | 岩野 | 1 | 8 | 1200 | | 4 | 3 | 武内 | 2 | 12 | 980 | | 4 | 1 | 青木 | 3 | 6 | 980 | | 4 | 4 | 中津 | 4 | 10 | 980 | | 2 | 4 | 中津 | 5 | 2 | 1250 | | 3 | 3 | 武内 | 6 | 5 | 1370 | | NULL | 5 | 山口 | NULL | NULL | NULL | +---------+-------------+--------+---------+----------+-------+
上の3つのJOINを使ったSQLを上手くいじるとシンプルに問題が解けるはずです。
もしかしたら、JOINを使わずに解けてしまうかもしれませんが、今回は結合の話
なので、解答ではJOINを使います。もちろん読者の皆様は使わなくても良いです。
解答は下へスクロールしていくとあります。
※ヒント SUMとGROUP BYを使います
解答
SELECT employees.name, SUM(sales.quantity * items.price) FROM employees LEFT JOIN sales USING (employee_id) LEFT JOIN items USING (item_id) GROUP BY employees.employee_id;
+--------+-----------------------------------+ | name | SUM(sales.quantity * items.price) | +--------+-----------------------------------+ | 青木 | 5880 | | 岩野 | 9600 | | 武内 | 18610 | | 中津 | 12300 | | 山口 | NULL | +--------+-----------------------------------+
このSQLが唯一の正解ではなく、読者様がもっと良いSQLを書いている
可能性は大いにあります。
現に、解答のSQLは処理速度などまったく気にしていません。
自分で考えたロジックを大切にしましょう。
※ 注意：MySQLのバージョンが5.7以上だとONLY_FULL_GROUP_BYがデフォルトでオンになっているようで、sql_modeを上書きするか、
SELECTするものをすべてGROUP BYに書かなくてはならないようです。気をつけましょう。
