外部結合

広告

外部結合はそれぞれのテーブルの指定した列の値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得します。基本となる構文は次の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 :

codecateidname
B0011旅行に行こう
D0012地球の歴史
F0013栗きんとん
D0022宇宙旅行
F0023ロールケーキ
D0032七人の勇者
B0021三国志
S0015表計算ソフト
B0031趣味と実益

cate :

idname
1和書
2DVD
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」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。

codecateidnameidname
B0011旅行に行こう1和書

同じように「goods」テーブルのデータを順に結合を行っていきます。もし「goods」テーブルの「cateid」カラムの値が「cate」テーブルの「id」カラムの値の中に一致するものがなかった場合もデータを取得します。この時、「cate」テーブル側には対応するデータがありませんので、カラムの値をNULLとしてデータを取得します。

codecateidnameidname
B0011旅行に行こう1和書
D0012地球の歴史2DVD
F0013栗きんとん3食品
D0022宇宙旅行2DVD
F0023ロールケーキ3食品
D0032七人の勇者2DVD
B0021三国志1和書
S0015表計算ソフトNULLNULL
B0031趣味と実益1和書

今度はRIGHT JOINの場合を例にするとデータの取得は次のように行われます。

まず「cate」テーブルの最初のデータの「id」カラムの値に対して、「goods」テーブルの「cateid」カラムの中に同じ値があるかどうかを調べます。存在した場合は「cate」テーブルのデータと「goods」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。

codecateidnameidname
B0011旅行に行こう1和書

今回の場合、「cate」テーブルの最初のデータの「id」カラムの値と一致する値を持つデータが「goods」テーブルに3行含まれるため、それぞれに対して結合を行ってからデータとして取得します。

codecateidnameidname
B0011旅行に行こう1和書
B0021三国志1和書
B0031趣味と実益1和書

同じように「cate」テーブルのデータを順に結合を行っていきます。もし「cate」テーブルの「id」カラムの値が「goods」テーブルの「cateid」カラムの値の中に一致するものがなかった場合もデータを取得します。この時、「goods」テーブル側には対応するデータがありませんので、カラムの値をNULLとしてデータを取得します。

codecateidnameidname
B0011旅行に行こう1和書
B0021三国志1和書
B0031趣味と実益1和書
D0012地球の歴史2DVD
D0022宇宙旅行2DVD
D0032七人の勇者2DVD
F0013栗きんとん3食品
F0023ロールケーキ3食品
NULLNULLNULL4家電

このように結合されたデータの中から必要なカラムを指定してデータを取得します。

取得するカラムの指定方法

取得するカラムの指定方法は内部結合の場合と同じです。詳しくは「内部結合(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));

テーブルには次のようなデータを追加してあります。

p15-1

p15-2

「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを左外部結合(LEFT JOIN)してデータを取得します。

mysql> select * from goods left join cate on goods.cateid = cate.id;

p15-3

今度は右外部結合(RIGHT JOIN)してデータを取得します。

mysql> select * from goods right join cate on goods.cateid = cate.id;

p15-4

サンプル

もう一つ試してみます。次のようなテーブルを作成します。

mysql> create table goods_info (id int, name varchar(10), price int);
mysql> create table goods_stock (id int, stockcount int);

テーブルには次のようなデータを追加してあります。

p15-5

p15-6

「goods_info」テーブルと「goods_stock」テーブルのそれぞれの「id」カラムで左外部結合(LEFT JOIN)してデータを取得します。

mysql> select * from goods_info left join goods_stock using(id);

p15-7

「USING」を使用する場合、結合に使われたカラムが先頭に表示されます。この時、同じカラムは一度しか現れません。その後にそれぞれのテーブルの残りのカラムが表示されます。

MySQLの使い方の他の記事を見てみる

( Written by Tatsuo Ikura )

関連記事 (一部広告含む)