6.1 テーブルの結合(2)

6.1.3 外部結合

内部結合では、結合に使う列のデータが両方のテーブルにあるレコードのみ
出力されますが、外部結合では、どちらかのテーブルにしかデータがないレ
コードも出力されます。外部結合には、左外部結合と右外部結合があります。

SELECT 列名1 FROM テーブル名1 LEFT|RIGHT [OUTER]
JOIN テーブル名2 ON テーブル名1.列名1 = テーブル名2.列名2;

先ほどのテーブルとは少し異なるデータを使います。staffテーブルには、
bidが5のメンバーがいるとします。一方、branchテーブルには、idは4まで
しかありません。

mysql> SELECT * FROM staff;
+----+-----------+------+------+
| id | name | age | bid |
+----+-----------+------+------+
| 1 | Sato | 23 | 1 |
| 2 | Suzuki | 24 | 2 |
| 3 | Takahashi | 28 | 5 |
| 4 | Tanaka | 26 | 3 |
| 5 | Watanabe | 30 | 2 |
+----+-----------+------+------+
mysql> SELECT * FROM branch;
+----+------------+
| id | branchname |
+----+------------+
| 1 | Tokyo |
| 2 | Yokohama |
| 3 | Osaka |
| 4 | Nagoya |
+----+------------+

まず、左外部結合から見ていきましょう。左外部結合では、最初に（左側で）
指定した方のテーブルにあるレコードすべてを出力します。右側のテーブル
のレコードは、場合によってはNULLとなります。次の例では、左側のテー
ブルにあるbid = 5に対応するデータが右側のテーブルにないので、その部
分はNULLとなっています。

mysql> SELECT * FROM staff LEFT JOIN branch ON staff.bid =
branch.id;
+----+-----------+------+------+------+------------+
| id | name | age | bid | id | branchname |
+----+-----------+------+------+------+------------+
| 1 | Sato | 23 | 1 | 1 | Tokyo |
| 2 | Suzuki | 24 | 2 | 2 | Yokohama |
| 3 | Takahashi | 28 | 5 | NULL | NULL |
| 4 | Tanaka | 26 | 3 | 3 | Osaka |
| 5 | Watanabe | 30 | 2 | 2 | Yokohama |

右外部結合はその逆です。後に（右側で）指定した方のテーブルにあるレコ
ードをすべて出力します。

mysql> SELECT * FROM staff RIGHT JOIN branch ON staff.bid =
branch.id;
+------+----------+------+------+----+------------+
| id | name | age | bid | id | branchname |
+------+----------+------+------+----+------------+
| 1 | Sato | 23 | 1 | 1 | Tokyo |
| 2 | Suzuki | 24 | 2 | 2 | Yokohama |
| 5 | Watanabe | 30 | 2 | 2 | Yokohama |
| 4 | Tanaka | 26 | 3 | 3 | Osaka |
| NULL | NULL | NULL | NULL | 4 | Nagoya |
+------+----------+------+------+----+------------+

6.1.4 副問い合わせ

ある条件で検索したデータを使ってさらに検索を行いたい場合があります。
そのような、SELECT文の中で使われるSELECT文を副問い合わせ（サブクエリー）といいます。

SELECT 列名FROM テーブル名WHERE 列名IN (SELECT ～);

次の例では、age列が最大のレコードのname列を表示しています。

mysql> SELECT name FROM staff WHERE age IN
(SELECT MAX(age) FROM staff);
+----------+
| name |
+----------+
| Watanabe |
+----------+

処理を分析してみましょう。

mysql> SELECT MAX(age) FROM staff;
+----------+
| MAX(age) |
+----------+
| 30 |
+----------+
mysql> SELECT * FROM staff WHERE age = 30;
+----+----------+------+------+
| id | name | age | bid |
+----+----------+------+------+
| 5 | Watanabe | 30 | 2 |
+----+----------+------+------+

6.1.5 自己結合

同一のテーブルに別名を付けて結合することを自己結合といいます。

SELECT 列名FROM テーブル名AS 別名1 JOIN テーブル名AS 別名2;

自己結合の例を見てみましょう。

mysql> SELECT * FROM staff AS a JOIN staff AS b;
+----+-----------+------+------+----+-----------+------+------+
| id | name | age | bid | id | name | age | bid |
+----+-----------+------+------+----+-----------+------+------+
| 1 | Sato | 23 | 1 | 1 | Sato | 23 | 1 |
| 2 | Suzuki | 24 | 2 | 1 | Sato | 23 | 1 |
| 3 | Takahashi | 28 | 5 | 1 | Sato | 23 | 1 |
| 4 | Tanaka | 26 | 3 | 1 | Sato | 23 | 1 |
| 5 | Watanabe | 30 | 2 | 1 | Sato | 23 | 1 |
| 1 | Sato | 23 | 1 | 2 | Suzuki | 24 | 2 |
| 2 | Suzuki | 24 | 2 | 2 | Suzuki | 24 | 2 |
| 3 | Takahashi | 28 | 5 | 2 | Suzuki | 24 | 2 |
| 4 | Tanaka | 26 | 3 | 2 | Suzuki | 24 | 2 |
| 5 | Watanabe | 30 | 2 | 2 | Suzuki | 24 | 2 |
| 1 | Sato | 23 | 1 | 3 | Takahashi | 28 | 5 |
| 2 | Suzuki | 24 | 2 | 3 | Takahashi | 28 | 5 |
| 3 | Takahashi | 28 | 5 | 3 | Takahashi | 28 | 5 |
| 4 | Tanaka | 26 | 3 | 3 | Takahashi | 28 | 5 |
| 5 | Watanabe | 30 | 2 | 3 | Takahashi | 28 | 5 |
| 1 | Sato | 23 | 1 | 4 | Tanaka | 26 | 3 |
| 2 | Suzuki | 24 | 2 | 4 | Tanaka | 26 | 3 |
| 3 | Takahashi | 28 | 5 | 4 | Tanaka | 26 | 3 |
| 4 | Tanaka | 26 | 3 | 4 | Tanaka | 26 | 3 |
| 5 | Watanabe | 30 | 2 | 4 | Tanaka | 26 | 3 |
| 1 | Sato | 23 | 1 | 5 | Watanabe | 30 | 2 |
| 2 | Suzuki | 24 | 2 | 5 | Watanabe | 30 | 2 |
| 3 | Takahashi | 28 | 5 | 5 | Watanabe | 30 | 2 |
| 4 | Tanaka | 26 | 3 | 5 | Watanabe | 30 | 2 |
| 5 | Watanabe | 30 | 2 | 5 | Watanabe | 30 | 2 |
+----+-----------+------+------+----+-----------+------+------+