内部結合と外部結合[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に書かなくてはならないようです。気をつけましょう。
参考
PHPのforeachの罠
foreachの$valueは参照渡しではない
$arrayに格納されている要素を、すべて空文字列に置き換えたいとします。
方法はいろいろありますが、今回はforeachの話なので、foreachを使います。
<?php $array = array("php", "python", "ruby", "javascript"); foreach ($array as $value) { $value = ""; } var_dump($array);
実行結果は次のようになります。
array(4) {
[0]=>
string(3) "php"
[1]=>
string(6) "python"
[2]=>
string(4) "ruby"
[3]=>
string(10) "javascript"
}
要素はそのままになっていますね。これは、$valueを直接変更したからです。
PHPの場合、foreachで取り出した要素は、その要素をコピーする形になります。
$valueは、$arrayの要素と同じアドレスを見ていない、というわけです。
解決策1
上記では、値渡しになっているので、参照渡しにすれば良い、という考え方です。
<?php $array = array("php", "python", "ruby", "javascript"); foreach ($array as &$value) { $value = ""; } unset($value);
$valueの前に&をつけて参照渡しにしています。
こうすることで、すべての要素を空文字列にすることができました。
注意点として、処理後、unset($value)で参照渡しをクリアしてやることが大事です。
処理したあとも、参照渡しである状態が保持されつづけると、思わぬバグの温床となります。
解決策2
参照渡しにするのではなく、$arrayの方を直接書き換える、という手法になります。
<?php $array = array("php", "python", "ruby", "javascript"); foreach ($array as $key => $value) { $array[$key] = ""; }
キーを取得して、そのキーで要素を指定することによって配列を操作しています。
僕はこちらの方をよく使っています。
PDOのfetch系メソッド
前提
- PHP 5.6
- mysql Ver 14.14 Distrib 5.7.18
- Google Chromeでしかテストしていません
- PDOのfetch系メソッドをまとめただけです
テストデータを用意
※ fetch系メソッドだけ調べたい方は下の方まで飛ばしてください
ターミナルでMySQLを起動。それから以下の命令を実行します。
create database fetch_test default charset utf8;
もちろんphpMyAdminからでもOKです。
show databases;で作成できたか確認できます。databasesと複数形であることに注意しましょう。
確認ができたら、use fetch_testでDBを選択します。
文房具を管理するitemsテーブルを作成します。
create table items ( id int auto_increment not null primary key, name varchar(30), price int(30) );
show tables で確認できます。 こちらもDBを表示した時と同じくtablesと複数形であることに注意です。
ここでようやくテストデータの挿入です。
適当にinsertしていきます。
insert into items (name, price) values ('鉛筆', 100); insert into items (name, price) values ('ノート', 120); insert into items (name, price) values ('消しゴム', 50);
終わったらselect * from items;でチェックしておきましょう。
| id | name | price |
|---|---|---|
| 1 | 鉛筆 | 100 |
| 2 | ノート | 120 |
| 3 | 消しゴム | 50 |
メソッドを切り替えてfetch系メソッドを検証するクラスをつくる
同じディレクトリ下に
という３つのファイルを作成します。
database.php
<?php class Database { public static function connectDB() { $pdo = ''; try { $pdo = new PDO( // 環境によってはhost部分をlocalhostにしてください 'mysql:dbname=fetch_test;host=127.0.0.1;charset=utf8', 'root', '', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ] ); } catch (PDOException $e) { header('Content-Type: text/plain; charset=UTF-8', true, 500); exit($e->getMessage()); } return $pdo; } }
静的メソッドでデータベースに接続するだけのクラスです。本来はDB名、ユーザ名、パスワードなど
は別ファイルに定数として分割するものですが、ここでは省略します。
PDO::ATTR_DEFAULT_FETCH_MODEはPDOのfetch系メソッドで引数が省略された場合などのフェッチスタイルを決定します。
ここではPDO::FETCH_ASSOCで設定しています。これは、カラム名をキーとする連想配列で取得するモードになります。
また、コメントアウトにもあるとおり、環境によってはhostの部分を127.0.0.1からlocalhostにしてください。僕の環境では逆で
ないと動きませんでした。
fetchTester.php
<?php require_once 'database.php'; class FetchTester { private $pdo; private $table = 'items'; function __construct() { $this->pdo = Database::connectDB(); } public function fetch() { $stmt = $this->selectTable(); while ($row = $stmt->fetch()) { var_dump($row); } } public function fetchObject() { $stmt = $this->selectTable(); while ($row = $stmt->fetchObject()) { var_dump($row); } } public function fetchColumn($fetchColumnNumber) { $stmt = $this->selectTable(); while (false !== $val = $stmt->fetchColumn($fetchColumnNumber)) { var_dump($val); } } public function fetchAll() { $stmt = $this->selectTable(); $rows = $stmt->fetchAll(); var_dump($rows); } private function selectTable() { $sql = 'select * from ' . $this->table; $stmt = $this->pdo->query($sql); return $stmt; } }
fetch系メソッドをそれぞれクラス固有のメソッドに分けています。唯一、前処理に当たるselectTableのみprivateで定義し、
残りのアクセス権はindex.phpで使えるようにpublicにしてあります。
index.php
<?php require_once 'FetchTester.php'; $fetch_tester = new FetchTester(); // この部分のメソッドを変更して使う $fetch_tester->fetchAll();
実際に実行するメソッドを実装するファイルです。動作を確かめる際、このファイルへアクセスします。
ここではfetchAllになっていますが、動作を確認したいfetch系メソッド名に切り替えることで、その動きをvar_dump形式で確認することができます。var_dumpが嫌だ、という方はechoなりprintなりに書きかえてもらって構いません。値をreturnする形式にして汎用性を高めても良いです。
1. fetch
では、順番に見ていきましょう。
まず基本形のfetchメソッドです。
$fetch_tester->fetch();
出力結果は以下のようになります。
array(3) {
["id"]=>
string(1) "1"
["name"]=>
string(6) "鉛筆"
["price"]=>
string(3) "100"
}
array(3) {
["id"]=>
string(1) "2"
["name"]=>
string(9) "ノート"
["price"]=>
string(3) "120"
}
array(3) {
["id"]=>
string(1) "3"
["name"]=>
string(12) "消しゴム"
["price"]=>
string(2) "50"
}
fetchは取得し終えるとfalseを返すので、それを条件分岐として利用することができます。
fetchTester.phpでは以下の部分です。
while ($row = $stmt->fetch()) {
// 処理
}
2. fetchObjcet
連想配列ではなく、オブジェクトとしてデータを取得します。
$fetch_tester->fetchObject();
結果は以下のような感じ。
object(stdClass)#4 (3) {
["id"]=>
string(1) "1"
["name"]=>
string(6) "鉛筆"
["price"]=>
string(3) "100"
}
object(stdClass)#5 (3) {
["id"]=>
string(1) "2"
["name"]=>
string(9) "ノート"
["price"]=>
string(3) "120"
}
object(stdClass)#4 (3) {
["id"]=>
string(1) "3"
["name"]=>
string(12) "消しゴム"
["price"]=>
string(2) "50"
}
3. fetchColumn
引数で指定した番号のカラムを文字列で取得します。省略時は０を指定したとみなされます。
$fetch_tester->fetchColumn();
今回使用しているfetchTester.phpでは、fetchTesterクラスのfetchColumnメソッドの引数から、PDO
のfetchColumnメソッドの引数へとカラム番号を受け渡す形になるよう実装してあります。
while内で、false !==としているのは値に0が入る場合を考慮してです。参考にさせていただいたサイトのソースコードが
しっかりしていると思ったので、そのまま採用させていただきました。
public function fetchColumn($fetchColumnNumber = 0) {
$stmt = $this->selectTable();
while (false !== $val = $stmt->fetchColumn($fetchColumnNumber)) {
var_dump($val);
}
}
以下は、引数に2を指定した場合の結果。
string(3) "100" string(3) "120" string(2) "50"
4. fetchAll
2次元配列として全データを取得します。
$fetch_tester->fetchAll();
ブラウザには以下のように出力されます。
array(3) {
[0]=>
array(3) {
["id"]=>
string(1) "1"
["name"]=>
string(6) "鉛筆"
["price"]=>
string(3) "100"
}
[1]=>
array(3) {
["id"]=>
string(1) "2"
["name"]=>
string(9) "ノート"
["price"]=>
string(3) "120"
}
[2]=>
array(3) {
["id"]=>
string(1) "3"
["name"]=>
string(12) "消しゴム"
["price"]=>
string(2) "50"
}
}
