SELECT構文：JOINを使ってテーブルを結合する

JOINは複数のテーブルを連結させるために用意されたキーワードです。単純にテーブルを連結させる以外にも、LEFT JOIN ONやUSINGなどの便利なキーワードがあるので、いろいろ試してみましょう。

リレーションによる関係付け

テーブルの作成とデータの挿入

ここでは、購入履歴テーブル（テーブル名=purchase）、商品テーブル（テーブル名=goods）、顧客テーブル（テーブル名=customer）を作成し、それを使って説明していきます。CREATE TABLEとINSERTを使って、テーブルの作成とデータの挿入をしておきましょう。

テーブルを作成する際の手順は、CREATE TABLEに続き、作成するテーブル名を指定します。次に、カッコ内にフィールドの定義を列挙していきます。フィールドの定義はフィールド名とデータ型、それに制約などを必要なだけ宣言します。

CREATE TABLE構文

CREATE TABLE テーブル名 (
    # 作成定義と制約
    フィールド名 データ型 オプション,
    フィールド名 データ型 オプション,
    ...
);

INSERTは、テーブルに新しいレコードを挿入します。新しいレコードを挿入するには、VALUESとSETの2種類があります。VALUESの場合は値をテーブルを構成する全フィールドに対応するように順番に指定し、SETはフィールド名と値のペアで必要な分だけ指定していきます。

INSERT構文

INSERT INTO テーブル名 VALUES ( 値, 値, ...), ...

INSERT INTO テーブル名 SET フィールド名=値, フィールド名=値, ...

以上の構文を使って、2つのテーブルを定義し、データを挿入します。

# 購入履歴テーブルの作成
CREATE TABLE purchase (
    id_p int unsigned NOT NULL auto_increment,
    id_c int unsigned NOT NULL,
    id_g int unsigned NOT NULL,
    quantity tinyint unsigned,

    PRIMARY KEY (id_p)
);

# データの挿入
INSERT purchase SET id_c=3, id_g=2, quantity=1;
INSERT purchase SET id_c=1, id_g=1, quantity=3;
INSERT purchase SET id_c=4, id_g=1, quantity=2;
INSERT purchase SET id_c=2, id_g=4, quantity=1;
INSERT purchase SET id_c=4, id_g=2, quantity=1;
INSERT purchase SET id_c=2, id_g=1, quantity=1;

# 商品テーブルの作成
CREATE TABLE goods (
    id_g tinyint(3) unsigned NOT NULL auto_increment,
    name varchar(30),
    price decimal(9,0),

    PRIMARY KEY (id_g)
);

# データの挿入
INSERT goods SET name='LEGO',price='3000';
INSERT goods SET name='LUMINOX WATCH',price='28000';
INSERT goods SET name='PS2',price='25000';
INSERT goods SET name='PDA',price='50000';

上記のクエリで作成されるテーブルは次のようになります。

購入履歴（purchase）テーブル

購入ID (id_p)	顧客ID (id_c)	商品ID (id_g)	数量 (quantity)
1	3	2	1
2	1	1	3
3	4	1	2
4	2	4	1

顧客（customer）テーブル

顧客ID (id_c)	姓 (sei)	年齢 (age)	性別 (sex)	メールアドレス (email)
1	エラ・フィッツジェラルド	48	2	ella@exsample.jp
2	トミー・ゲレロ	34	1	tommy@exsample.jp
3	マディ・ウォーターズ	55	1	muddy@exsample.jp
4	エディ・ロバーツ	28	1
5	マーサ・リーブス	38	2	martha@exsample.jp
6	スライ･ストーン	34	1

商品（goods）テーブル

商品ID (id_g)	商品名 (name)	価格 (price)
1	Afro-American-Arctic	2300
2	Ready To Die	1800
3	Fresh	2300
4	Things Fall Apart	1600

購入履歴テーブルは顧客テーブルのプライマリキー（プライマリ・キー）の[顧客ID(id_c)]フィールドと、商品テーブルのプライマリキーの[商品ID(id_g)]フィールドと同じ名前のフィールドを用意していて、レコードの追加時にそれぞれのID情報を記録することができるようになっています。

---

リレーションの作成

購入履歴（purchase）テーブル、顧客（customer）テーブル、商品（goods）テーブルの中で、中心となるのが購入履歴テーブルです。購入履歴テーブルは顧客テーブルのプライマリキーと、商品テーブルのプライマリキーと同じフィールドを持っていて、顧客テーブルと商品テーブルのプライマリキーを格納することができます。このように、他のテーブルのプライマリキーを参照するフィールドのことを外部キーと呼びます。購入履歴テーブルの場合は[顧客ID(id_c)]と[商品ID(id_g)]が外部キーにあたります。

3つのテーブル間のリレーションは次の図のようになっています。

各テーブル間のリレーション

顧客履歴テーブルは購入情報を記録するためのテーブルで、購入処理が正しく処理された際に、データ登録が行われます。その際、購入に関連するデータ、数量や日付などとともに、購入したユーザの[顧客ID(id_c)]と、購入された商品の[商品ID(id_g)]が記録されます。

顧客テーブルからは姓名や年齢、商品テーブルからは商品名や価格などを取り出すことが可能です。

結合後のテーブル

---

JOIN構文

複数のテーブルから情報を取得する必要がある際、テーブルを連結することでクエリの発行から情報の取得までを一度で済ませることができます。複数のテーブルを連結させるには、FROMで複数のテーブルを指定することで実現することができます。複数のテーブルを指定するには、テーブル名とテーブル名の間にカンマ( , )、もしくはJOINを挟みます。

JOINを使って同様のことが行えます。

次のクエリは、購入履歴(purchase)テーブルと顧客(customer)テーブルを結合し、[購入ID(id_p)] フィールド、[名前(fullname)] フィールドを表示します。

SELECT id_p, fullname
FROM purchase JOIN customer ON purchase.id_c=customer.id_c;
+------+--------------------------------------+
| id_p | fullname |
+------+--------------------------------------+
| 1 | マディ・ウォーターズ |
| 2 | エラ・フィッツジェラルド |
| 3 | エディ・ロバーツ |
| 4 | トミー・ゲレロ |
| 5 | エディ・ロバーツ |
| 6 | トミー・ゲレロ |
+------+--------------------------------------+

購入履歴(purchase)テーブルと顧客(customer)テーブルを結合するために、JOINを使っています。ON以降の「purchase.id_c = customer.id_c」という箇所は、「顧客履歴テーブルと顧客テーブルの[顧客ID(id_c)]フィールドの値が一致したレコードを選択する」という意味です。複数のテーブルにリレーションを張るには、FROMの部分に複数のテーブルをカンマ( , )かJOINで区切って並べ、ONでそれらのテーブルを連結する条件を指定します。

内部結合

JOIN、カンマ( , )は2つのテーブルを比較し、結合条件に一致した行だけを返します。これを内部結合と呼び、他の結合方法と区別しやすいように、INNER JOINと記述することも可能です。JOIN、カンマ( , )、INNER JOINはどれも意味は同じです。

購入履歴(purchase)テーブルと商品(goods)テーブルを[商品ID（id_g）]で内部結合してみましょう。

内部結合は2つのテーブルを比較し、結合条件に一致したレコードだけを返す結合方法です。どちらかのテーブルに無いレコードは表示されないので、購入履歴テーブルに記録の無い商品、ここでは[商品ID（id_g）]が3のレコードは表示されません。

外部結合

内部結合が両方のテーブルを比較し、結合条件に一致したレコードだけを返す結合方法だったのに対し、外部結合は、結合条件に一致した行に加え、指定したテーブルに関しては結合条件に一致しなくてもレコードを返す結合方法です。

外部結合には、LEFT JOINキーワードとRIGHT JOINキーワードの2種があります。JOINを中心にして、JOINの前に記述したテーブルを左、JOINの後に記述したテーブルを右として、LEFT JOINは左側のテーブルが結合条件に一致しなくてもレコードを返し、RIGHTJOINは右側のテーブルが結合条件に一致しなくてもレコードを返します。

それでは、購入履歴(purchase)テーブルと商品(goods)テーブルを[商品ID（id_g）]で外部結合してみましょう。ここれではLEFT OUTER JOINを使います。OUTERは省略可能です。

SELECT purchase.id_g,name
FROM goods LEFT OUTER JOIN purchase ON purchase.id_g=goods.id_g;
+------+----------------------+
| id_g | name |
+------+----------------------+
| 1 | Afro-American-Arctic |
| 1 | Afro-American-Arctic |
| 1 | Afro-American-Arctic |
| 2 | Ready To Die |
| 2 | Ready To Die |
| NULL | Electric Ladyland |
| 4 | Things Fall Apart |
| NULL | Maggot Brain |
| NULL | We Won't Stop |
+------+----------------------+

LEFT OUTER JOINキーワードを使っているので、左に指定した商品(goods)テーブルは条件に一致しなくてもレコードが返されます。

JOIN構文

テーブル名, テーブル名
テーブル名 JOIN テーブル名
テーブル名 INNER JOIN テーブル名 JOIN条件式
テーブル名 STRAIGHT_JOIN テーブル名
テーブル名 LEFT [OUTER] JOIN テーブル名 JOIN条件式
テーブル名 LEFT [OUTER] JOIN テーブル名
テーブル名 NATURAL [LEFT [OUTER]] JOIN テーブル名

{ oj テーブル名 LEFT OUTER JOIN テーブル名 ON 条件式 }
テーブル名 RIGHT [OUTER] JOIN テーブル名 JOIN条件式
テーブル名 RIGHT [OUTER] JOIN テーブル名
テーブル名 NATURAL [RIGHT [OUTER]] JOIN テーブル名

[JOIN]

JOINとカンマ( , )は、同義語です。どちらもテーブル間の直積です。

[テーブル名]

[テーブル名] は次のように定義と同じです。

テーブル名 [[AS] エイリアス名] [USE INDEX (キー・リスト)] [IGNORE INDEX (キー・リスト)]

[テーブル名] は、テーブル名 AS エイリアス名や "テーブル名 エイリアス名" によるエイリアス名を指定することができます。

SELECT t1.sei, t2.quantity
FROM customer AS t1, goods AS t2
WHERE t1.id_c = t2.id_c;

[JOIN条件式]

[JOIN条件式] は次のような定義と同じです。

ON 条件式 | USING (フィールド・リスト)

[LEFT OUTER JOIN]

LEFT OUTER JOIN は、ODBCとの互換性のために用意されています。

[OUTER]

外部結合を明示するためのキーワードで、省略可能です。

---

データベースオブジェクトの参照

JOINを使って購入履歴テーブルと顧客テーブルを結合する際に注意したいのは、購入履歴テーブルと顧客テーブルの両方で使われるフィールド名をSELECT句に指定する際は、どちらかのテーブル名で修飾する必要があるということです。テーブル名の記述がないと、DBMSはどちらのテーブルのフィールドを表示すればよいのか判断できず、エラーとなります。

下記クエリでは、購入履歴テーブルと顧客テーブルで共通して使われる[顧客ID(id_c)]をテーブル名で修飾せずに記述しているので、DBMSからエラーメッセージを返されています（DBMSによってエラーメッセージは異なります）。

フィールド名をテーブル名で修飾するには、ドット（ . ）でテーブル名とフィールド名をつなげます。

SELECT テーブル名1.フィールド名, テーブル名2.フィールド名
FROM テーブル名1, テーブル名2

[顧客ID(id_c)]を「purchase.id_c」と購入履歴のテーブル名で修飾した結果が下記のとおりです。

---

JOIN構文のキーワード

これから紹介するオプションで重要なのは、LEFT JOIN ON、USING、NATURAL LEFT JOIN までです。STRIGHT JOIN を使う機会はほとんどありませんし、RIGHT JOIN、INNER JOIN は余分です。

LEFT JOIN ON

リレーションを設定したテーブル結合の場合、通常はリレーションが成立しなかったレコードは表示されません。LEFT JOINを宣言すると、右側のテーブルにマッチするレコードが無かった場合でも、レコードが表示されます。その際の右側のテーブルのフィールド値はすべてNULLです。

レコード選択の条件式はWHEREの代わりにONを使います。ONとWHEREの書式は同じです。

テーブル名 LEFT [OUTER] JOIN テーブル名 ON 条件式

次のクエリは、購入履歴(purchase)テーブルと顧客(customer)テーブルを[顧客ID(id_c)]フィールドで結合しています。

SELECT id_p, fullname
FROM customer JOIN purchase
ON purchase.id_c = customer.id_c;
+------+--------------------------------------+
| id_p | fullname |
+------+--------------------------------------+
| 1 | マディ・ウォーターズ |
| 2 | エラ・フィッツジェラルド |
| 3 | エディ・ロバーツ |
| 4 | トミー・ゲレロ |
| 5 | エディ・ロバーツ |
| 6 | トミー・ゲレロ |
+------+--------------------------------------+

実行結果では、購入履歴テーブルに記録のない顧客は表示されていません。

次のクエリは、LEFT JOINに変更した場合で、購入履歴テーブルに記録されていない顧客も表示されています。

SELECT id_p, fullname
FROM customer LEFT JOIN purchase
ON purchase.id_c = customer.id_c;
+------+--------------------------------------+
| id_p | fullname |
+------+--------------------------------------+
| 2 | エラ・フィッツジェラルド |
| 4 | トミー・ゲレロ |
| 6 | トミー・ゲレロ |
| 1 | マディ・ウォーターズ |
| 3 | エディ・ロバーツ |
| 5 | エディ・ロバーツ |
| NULL | マーサ・リーブス |
| NULL | スライ･ストーン |
+------+--------------------------------------+

2つ以上のテーブル結合でもLEFT JOINは使えます。購入履歴(purchase)テーブルを中心にして、顧客(customer)テーブルと商品(goods)テーブルからデータを取得します。その際、顧客(customer)テーブルにあるレコードは全て表示するようにLEFT JOINで指定します。

---

USING

USING句は、ON句の略記法で、結合する条件が同じ名前のフィールド名であれば、USINGで結合条件を指定することができます。USINGの引数に指定したフィールド名で、テーブル間のリレーションを作成します。

次の構文は意味的には同じです。

# USING を使った構文
SELECT * FROM A LEFT JOIN B USING (Ｆ1)

# ON 条件式を使った構文
SELECT * FROM A LEFT JOIN B ON A.F1=B.F1

フィールド名をカンマ( , )で区切って条件式を複数指定することも可能です。

# USING を使った構文
SELECT * FROM A LEFT JOIN B USING (Ｆ1, F2, F3)

# ON 条件式を使った構文
SELECT * FROM A LEFT JOIN B ON A.F1=B.F1 AND A.F2=B.F2 AND A.F3=B.F3,...

次のクエリは、購入履歴(purchase)テーブルと顧客(customer)テーブルを[顧客ID(id_c)]フィールドで結合し、なおかつ顧客(customer)テーブルにあるレコードをすべて表示します。

---

NATURAL LEFT JOIN

同じフィールド名を持つ2つのテーブルの NATURAL [LEFT] JOINは、 USINGを伴ったINNER JOINやLEFT JOINと同じです。

テーブル名 NATURAL [LEFT [OUTER]] JOIN テーブル名
テーブル名 NATURAL [RIGHT [OUTER]] JOIN テーブル名

次のクエリは、購入履歴(purchase)テーブルと顧客(customer)テーブルを[顧客ID(id_c)]フィールドで結合し、なおかつ顧客(customer)テーブルにあるレコードをすべて表示します。

---

STRAIGHT JOIN

STRAIGHT JOIN は、常に左側のテーブルを先に読むことを除けば、JOIN と同じです。これは、不当な順序でテーブルを出力するようなまれな事態に有効ですが、ほとんどの場合は必要ありません。

テーブル名 STRAIGHT JOIN テーブル名

---

INNER JOIN

INNER JOINとカンマ( , )は、同義語で、通常のJOINの別名です。

テーブル名 INNER JOIN テーブル名 ON 条件式

次の 2 つのSQLは、同じ意味です。

---

OUTER JOIN

デフォルトのJOINは、リレーションを作成した際、リレーションが作成できなかったレコードに対しては表示しません。たとえば、次のようにリレーションを作成した場合、購入履歴(purchase)テーブルに情報がない[顧客ID（id_c）]の5番、6番は表示されません。

これは、[顧客ID（id_c）]の5番、6番は購入したことがなく、購入履歴(purchase)テーブルに情報がないためです。購入履歴(purchase)テーブルに登録されていない顧客情報なので、リレーションを作成した際に選択レコードからはずされています。これを、INNER JOIN（内部結合）と呼びます。

しかし、場合によっては、注文が無い顧客が無いということを明示した上で、リストには表示してほしいことがあります。これを実現するのが、OUTER JOIN（外部結合）です。

SELECT id_p, customer.id_c, fullname
FROM customer LEFT OUTER JOIN purchase
ON purchase.id_c = customer.id_c;
+------+------+--------------------------------------+
| id_p | id_c | fullname |
+------+------+--------------------------------------+
| 2 | 1 | エラ・フィッツジェラルド |
| 4 | 2 | トミー・ゲレロ |
| 6 | 2 | トミー・ゲレロ |
| 1 | 3 | マディ・ウォーターズ |
| 3 | 4 | エディ・ロバーツ |
| 5 | 4 | エディ・ロバーツ |
| NULL | 5 | マーサ・リーブス |
| NULL | 6 | スライ･ストーン |
+------+------+--------------------------------------+

今度は[顧客ID（id_c）]の5番、6番が表示されましたが、購入履歴がないため、[購入履歴ID（id_p）]はNULLと表示されています。

---

RIGHT JOIN

RIGHT JOINはLEFT JOINが左のテーブルを必ず出力させるのと逆に、右側のテーブルを必ず出力しようとします。どちらを使ってもよいのですが、LEFT JOIN、RIGHT JOINの両方を使うと混乱を招くことになるので、LEFT JOINのみを使うことをオススメします。

---

JOINの使い方

2つ以上のテーブルの結合

リレーションは、2つ以上のテーブルに対しても処理できます。手順はほとんど同じで、FROMに結合するテーブル名を並べ、ONで連結する条件を指定します。

購入履歴(puarchase)テーブル、顧客(customer)テーブル、商品(goods)テーブルを結合し、[購入ID(id_p)]フィールド、[名前(fullname)]フィールド、[商品名(name)]フィールドを表示するクエリを紹介します。

SELECT id_p, fullname, name
FROM purchase JOIN customer JOIN goods
ON purchase.id_c = customer.id_c
And purchase.id_g = goods.id_g;
+------+--------------------------------------+----------------------+
| id_p | fullname | name |
+------+--------------------------------------+----------------------+
| 1 | マディ・ウォーターズ | Ready To Die |
| 2 | エラ・フィッツジェラルド | Afro-American-Arctic |
| 3 | エディ・ロバーツ | Afro-American-Arctic |
| 4 | トミー・ゲレロ | Things Fall Apart |
| 5 | エディ・ロバーツ | Ready To Die |
| 6 | トミー・ゲレロ | Afro-American-Arctic |
+------+--------------------------------------+----------------------+

購入履歴(purchase)テーブルを中心にして、[顧客ID(id_c)]フィールドと[商品ID(id_g)]フィールドを使ってリレーションを作成しています。上記の場合、AND演算子で条件式をつなげているので、２つのリレーションが成立するレコードだけが選択されます。

---

複雑なリレーションの作成

テーブル名のエイリアス

テーブルの結合を行う際は、テーブルのエイリアス名を付けることがよくあります。方法は、フィールド名のエイリアスと同様で、テーブル名の後ろにスペース( )で区切ってエイリアス名を指定するか、テーブル名とエイリアス名の間にASを挟みます。

SELECT id_p, fullname, name
FROM purchase AS p, customer, goods
ON purchase.id_c = customer.id_c
And purchase.id_g = goods.id_g;

自己結合：自己自身とリレーションを作成

1つのテーブルに登録されたレコードの中から、同じ値をもつレコードのペアを見つけたい場合があります。たとえば、顧客テーブルに登録された顧客データの中から、同じ名前や同じ住所の顧客がいれば、そのペア情報だけを抜き取りとりたいといった要望があった際などです。1つのテーブルで条件式を作成しようとすると、テーブル名で修飾しても「顧客テーブル.年齢=顧客テーブル.年齢」となり、名前の衝突が発生します。

自己自身とリレーションを作成したい際は、テーブル名の別名を利用して、1つのテーブルを2つのテーブルのように扱うことことで解決できます。このように、同じテーブルをエイリアスを使用して結合することを自己結合と呼びます。

次の例は、顧客(customer)テーブルから、同じ[年齢(age)]の値を持つ顧客のペアを選択します。FROM句のテーブル名が同じになるので、別名を付けてテーブル名を明確に区別します。

SELECT C1.fullname, C1.age, C2.fullname, C2.age
FROM customer AS C1 JOIN customer AS C2
ON C1.age=C2.age
AND C1.id_c < C2.id_c;
+-----------------------+-----+--------------------------+-----+
| fullname | age | fullname | age |
+-----------------------+-----+--------------------------+-----+
| トミー・ゲレロ | 34 | スライ･ストーン | 34 |
+-----------------------+-----+--------------------------+-----+

FROMで2つのテーブルを指定していますが、実際には顧客(customer)テーブルに、C1とC2という二つのエイリアス名を指定しています。

ON以降の箇所は少し特殊なことをしています。下記は再帰結合のために重複するレコードを排除するための条件式です。

AND C1.id_c < C2.id_c

※再帰結合とは、同一のテーブルを別名を使って結合することです。