外部結合を行う(LEFT JOIN句, RIGHT JOIN句)

2 つのテーブルを結合してデータを取得する方法の中で、指定したそれぞれのテーブルのカラムの値が一致するデータだけではなくどちらかのテーブルにだけデータがある場合も合わせて取得する方法が外部結合です。ここでは MySQL で外部結合を行うための LEFT JOIN 句および RIGHT JOIN句 の使い方について解説します。

(Last modified: )

外部結合とは

外部結合は内部結合と同じく2つのテーブルでそれぞれ結合の対象となるカラムを指定し、それぞれのカラムに同じ値が格納されているデータを結合して取得するものです。内部結合の場合は、一致しないデータは取得しませんでしたが、外部結合の場合は一致しない場合もデータとして取得します。

左側のテーブルにしかないデータも取得する方法を左外部結合、右側のテーブルにしかないデータも取得する方法を右外部結合といいます。

最初に左外部結合です。次の図を見て下さい。左側のテーブルと右側のテーブルを左外部結合します。結合の対象となるカラムは左側のテーブルが「部署ID」、右側のテーブルが「ID」です。この2つのカラムの値が同じデータ同士を結合し取得します。そして左外部結合の場合は左側のテーブルにしかないデータも併せて取得します。

外部結合とは(1)

次に右外部結合です。次の図を見て下さい。左側のテーブルと右側のテーブルを右外部結合します。結合したデータを取得するところは左外部結合と同じですが、右外部結合の場合は右側のテーブルにしかないデータも併せて取得します。

外部結合とは(2)

このように外部結合の場合はどちらかのテーブルにしかないデータについても取得する点が内部結合と異なります。

LEFT JOIN句およびRIGHT JOIN句の使い方

SELECT 文と LEFT JOIN 句を組み合わせることで2つのテーブルを左外部結合させてデータを取得することができます。書式は次の通りです。 

SELECT table_name.col_name1 [, table_name.col_name2 ...]
  FROM table_name1
  LEFT JOIN tbl_name2
  ON table_name1.col_name1 = table_name2.col_name2;

また SELECT 文と RIGHT JOIN 句を組み合わせることで2つのテーブルを右外部結合させてデータを取得することができます。書式は次の通りです。 

SELECT table_name.col_name1 [, table_name.col_name2 ...]
  FROM table_name1
  RIGHT JOIN tbl_name2
  ON table_name1.col_name1 = table_name2.col_name2;

SELECT 文で取得するデータは2つのテーブルを結合したものから取得します。取得するカラムはどちらのテーブルにあるどのカラムなのかが分かるように「テーブル名.カラム名」の形式で指定します。

どのように結合するのかは ON のあとに記述します。結合の対象となるカラムについて テーブル名1.カラム名1 = テーブル名2.カラム名2 の形式で指定します。

-- --

それでは実際に試してみます。 2 つのテーブルを作成します。 1 つ目のテーブルは次のように作成しました。社員に関するテーブルで、社員の名前と部署IDが登録されています。

create table staff (id int, name varchar(10), deptid int);

LEFT JOIN句およびRIGHT JOIN句の使い方(1)

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

insert into staff values (1, 'Yamada', 1);
insert into staff values (2, 'Honda', 4);
insert into staff values (3, 'Kudou', 6);
insert into staff values (4, 'Nishi', 1);
insert into staff values (5, 'Tagawa', 3);

LEFT JOIN句およびRIGHT JOIN句の使い方(2)

2 つ目のテーブルは次のように作成しました。部署に関するテーブルで、部署の名前が登録されています。

create table dept (id int, name varchar(10));

LEFT JOIN句およびRIGHT JOIN句の使い方(3)

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

insert into dept values (1, 'Develop');
insert into dept values (3, 'Legal');
insert into dept values (5, 'Planning');
insert into dept values (6, 'Marketing');

LEFT JOIN句およびRIGHT JOIN句の使い方(4)

-- --

それでは最初に左外部結合を行いデータを取得します。結合条件は staff テーブルの deptid カラムと dept テーブルの id カラムを結合します。次のように実行してください。

select * from staff left join dept on staff.deptid = dept.id;

LEFT JOIN句およびRIGHT JOIN句の使い方(5)

左外部結合で取得できるデータは 2 つのテーブルのデータが結合されたものになります。左外部結合の場合は、左側テーブルにしかないデータも取得し、対応する右側のテーブルのデータ部分には NULL が格納されます。

次に右外部結合を行いデータを取得します。結合条件は staff テーブルの deptid カラムと dept テーブルの id カラムを結合します。次のように実行してください。

select * from staff right join dept on staff.deptid = dept.id;

LEFT JOIN句およびRIGHT JOIN句の使い方(6)

右外部結合で取得できるデータも 2 つのテーブルのデータが結合されたものになります。右外部結合の場合は、右側テーブルにしかないデータも取得し、対応する左側のテーブルのデータ部分には NULL が格納されます。

なお内部結合の場合と異なり、どちらのテーブルにどちらのテーブルを結合するのかの順番が入れ替わると結果も異なります。これは左外部結合の場合は左側にだけあるデータも取得し、右外部結合の場合は右側にだけあるデータも取得するためです。

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

外部結合を行って SELECT 文でデータを取得する場合、複数のテーブルのカラムが存在するのでどのカラムの値を取得するか指定するときに「カラム名」ではなく「テーブル名.カラム名」のように指定します。

左外部結合の場合を使って確認してみます。先ほどのサンプルでカラム名を指定せずにデータを取得した場合は次のようになります。

select * from staff left join dept on staff.deptid = dept.id;

取得するデータのカラム指定方法(1)

取得したデータのカラムには id カラムが 2 つ、 name カラムも 2 つ含まれています。このように複数のテーブルに同じ名前のカラムがある場合にこのカラムをを指定して取得したい場合には次のように「テーブル名.カラム名」の形で指定します。

select staff.id, staff.name, dept.name from staff left join dept on staff.deptid = dept.id;

取得するデータのカラム指定方法(2)

どちらかのテーブルにしか含まれておらずテーブル名を省略してもどちらのテーブルのカラムか分かる場合には「テーブル名.」の部分を省略して「カラム名」だけでも可能です。(下記では deptid カラムがそれに当たります)。

select staff.id, staff.name, deptid, dept.name from staff left join dept on staff.deptid = dept.id;

取得するデータのカラム指定方法(3)

2つのテーブルに同じカラム名があるのにテーブル名を指定せずにカラム名だけを記述した場合は次のような Error: Column 'カラム名' in field list is ambiguous というエラーとなります。

select name from staff left join dept on staff.deptid = dept.id;

取得するデータのカラム指定方法(4)

結合するカラム名が二つのテーブルで同じ場合(USING)

SELECT 文で外部結合を行う時に結合条件を ON のあとに記載していましたが、結合するカラム名が二つのテーブルでまったく同じ場合には次の書式を使用することもできます。(下記は左外部結合の場合です)。 

SELECT table_name.col_name1 [, table_name.col_name2 ...]
  FROM table_name1
  LEFT JOIN tbl_name2
  USING (col_name, ...);

ON table_name1.col_name = table_name2.colname と記述する代わりに USING (col_name) と書くことができます。なお後で実際に試してみると確認できますが、 USING を使用すると取得するデータの形式が少し変わります。

では実際に試してみます。次のようなテーブルを作成し、データを追加しました。

create table staff (id int, name varchar(10), deptid int);

insert into staff values (1, 'Yamada', 1);
insert into staff values (2, 'Honda', 3);
insert into staff values (3, 'Kudou', 2);
insert into staff values (4, 'Ueda', 1);

結合するカラム名が二つのテーブルで同じ場合(USING)(1)

2 つ目のテーブルは次のように作成し、データを追加しました。

create table dept (deptid int, name varchar(10));

insert into dept values (1, 'Develop');
insert into dept values (2, 'Legal');
insert into dept values (3, 'Planning');

結合するカラム名が二つのテーブルで同じ場合(USING)(2)

それでは staff テーブルに dept テーブルを左外部結合します。結合条件は staff テーブルの deptid カラムと dept テーブルの deptid カラムを結合します。結合する両方のカラム名が同じなので次のように記述します。

select * from staff left join dept using(deptid);

結合するカラム名が二つのテーブルで同じ場合(USING)(3)

左外部結合したデータを取得することができました。 USING を使った場合に SELECT 文で取得するカラムとして * を使用すると取得するデータの先頭に結合条件で使用したカラム名が 1 回だけ表示されます。

-- --

左外部結合を行うための LEFT JOIN 句および右外部結合を行うための RIGHT JOIN 句の使い方について解説しました。

( Written by Tatsuo Ikura )

プロフィール画像

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。