外部結合
外部結合はそれぞれのテーブルの指定した列の値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得します。基本となる構文は次の2つが用意されています。
SELECT table_name.col_name, ... FROM tbl_name1 LEFT JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;
SELECT table_name.col_name, ... FROM tbl_name1 RIGHT JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;
基本的に内部結合の場合と同じですが、外部結合では結合の対象となっているカラムの値が一致しているデータに加えて、カラムの値がどちらかのテーブルにしかなかった場合でもデータとして取得します。この時、どちらのテーブルのデータを取得するかで2つの構文が用意されています。
LEFT JOINではFROMの後に書かれたテーブルのデータだけを取得します。RIGHT JOINではJOINの後に書かれたテーブルのデータだけを取得します。
なお結合するカラムの名前が同じ場合にはONの代わりにUSINGを使用して次のように記述することもできます。
SELECT table_name.col_name, ... FROM tbl_name1 LEFT JOIN tbl_name2 USING (col_name1, col_name2, ...);
SELECT table_name.col_name, ... FROM tbl_name1 RIGHT JOIN tbl_name2 USING (col_name1, col_name2, ...);
「USING (col_name)」というのは「ON tbl_name1.col_name = tbl_name2.col_name」と同じことです。
テーブルの結合
具体的な例で見てみます。次のような2つのテーブルを用意しました。
goods :
| code | cateid | name |
|---|---|---|
| B001 | 1 | 旅行に行こう |
| D001 | 2 | 地球の歴史 |
| F001 | 3 | 栗きんとん |
| D002 | 2 | 宇宙旅行 |
| F002 | 3 | ロールケーキ |
| D003 | 2 | 七人の勇者 |
| B002 | 1 | 三国志 |
| S001 | 5 | 表計算ソフト |
| B003 | 1 | 趣味と実益 |
cate :
| id | name |
|---|---|
| 1 | 和書 |
| 2 | DVD |
| 3 | 食品 |
| 4 | 家電 |
外部結合を行うには、2つのテーブルで結合の対象となるカラムを指定します。例えば次のように指定します。
SELECT * FROM goods LEFT JOIN cate ON goods.cateid = cate.id;
上記の場合、「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを使って結合します。(RIGHT JOINの場合も同様です)。
データの取得
LEFT JOINの場合を例にするとデータの取得は次のように行われます。
まず「goods」テーブルの最初のデータの「cateid」カラムの値に対して、「cate」テーブルの「id」カラムの中に同じ値があるかどうかを調べます。存在した場合は「goods」テーブルのデータと「cate」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
同じように「goods」テーブルのデータを順に結合を行っていきます。もし「goods」テーブルの「cateid」カラムの値が「cate」テーブルの「id」カラムの値の中に一致するものがなかった場合もデータを取得します。この時、「cate」テーブル側には対応するデータがありませんので、カラムの値をNULLとしてデータを取得します。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
| D001 | 2 | 地球の歴史 | 2 | DVD |
| F001 | 3 | 栗きんとん | 3 | 食品 |
| D002 | 2 | 宇宙旅行 | 2 | DVD |
| F002 | 3 | ロールケーキ | 3 | 食品 |
| D003 | 2 | 七人の勇者 | 2 | DVD |
| B002 | 1 | 三国志 | 1 | 和書 |
| S001 | 5 | 表計算ソフト | NULL | NULL |
| B003 | 1 | 趣味と実益 | 1 | 和書 |
今度はRIGHT JOINの場合を例にするとデータの取得は次のように行われます。
まず「cate」テーブルの最初のデータの「id」カラムの値に対して、「goods」テーブルの「cateid」カラムの中に同じ値があるかどうかを調べます。存在した場合は「cate」テーブルのデータと「goods」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
今回の場合、「cate」テーブルの最初のデータの「id」カラムの値と一致する値を持つデータが「goods」テーブルに3行含まれるため、それぞれに対して結合を行ってからデータとして取得します。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
| B002 | 1 | 三国志 | 1 | 和書 |
| B003 | 1 | 趣味と実益 | 1 | 和書 |
同じように「cate」テーブルのデータを順に結合を行っていきます。もし「cate」テーブルの「id」カラムの値が「goods」テーブルの「cateid」カラムの値の中に一致するものがなかった場合もデータを取得します。この時、「goods」テーブル側には対応するデータがありませんので、カラムの値をNULLとしてデータを取得します。
| code | cateid | name | id | name |
|---|---|---|---|---|
| B001 | 1 | 旅行に行こう | 1 | 和書 |
| B002 | 1 | 三国志 | 1 | 和書 |
| B003 | 1 | 趣味と実益 | 1 | 和書 |
| D001 | 2 | 地球の歴史 | 2 | DVD |
| D002 | 2 | 宇宙旅行 | 2 | DVD |
| D003 | 2 | 七人の勇者 | 2 | DVD |
| F001 | 3 | 栗きんとん | 3 | 食品 |
| F002 | 3 | ロールケーキ | 3 | 食品 |
| NULL | NULL | NULL | 4 | 家電 |
このように結合されたデータの中から必要なカラムを指定してデータを取得します。
取得するカラムの指定方法
取得するカラムの指定方法は内部結合の場合と同じです。詳しくは「内部結合(INNER JOIN句)」を参照して下さい。
例えば「goods」テーブルの「code」カラムと「name」カラム、そして「cate」テーブルの「name」カラムの値を取得する場合は次のように記述して下さい。
SELECT code, goods.name, cate.name FROM goods LEFT JOIN cate ON goods.cateid = cate.id;
サンプル
実際に試してみます。まず次のようなテーブルを作成します。
mysql> create table goods (code varchar(5), cateid int, name varchar(10));
mysql> create table cate (id int, name varchar(10));
テーブルには次のようなデータを追加してあります。
「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを左外部結合(LEFT JOIN)してデータを取得します。
mysql> select * from goods left join cate on goods.cateid = cate.id;
今度は右外部結合(RIGHT JOIN)してデータを取得します。
mysql> select * from goods right join cate on goods.cateid = cate.id;
サンプル
もう一つ試してみます。次のようなテーブルを作成します。
mysql> create table goods_info (id int, name varchar(10), price int);
mysql> create table goods_stock (id int, stockcount int);
テーブルには次のようなデータを追加してあります。
「goods_info」テーブルと「goods_stock」テーブルのそれぞれの「id」カラムで左外部結合(LEFT JOIN)してデータを取得します。
mysql> select * from goods_info left join goods_stock using(id);
「USING」を使用する場合、結合に使われたカラムが先頭に表示されます。この時、同じカラムは一度しか現れません。その後にそれぞれのテーブルの残りのカラムが表示されます。
( Written by Tatsuo Ikura )
DBOnline