外部結合(OUTER JOIN句)
2つのテーブルを結合してデータを取得する方法の中で、指定したそれぞれのテーブルのカラムの値が一致するデータだけではなくどちらかのテーブルにだけデータがある場合も合わせて取得する方法が外部結合です。ここでは外部結合を行うためのLEFT OUTER JOIN句の使い方について解説します。
1.LEFT OUTER JOIN句の使い方
2.データの結合方法
3.結合が複数のデータで一致する場合
4.取得するデータのカラム指定方法
LEFT OUTER JOIN句を使うための書式は次の通りです。
SELECT テーブル名.カラム名, ... FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
長いので簡略化すると次のようになります。
SELECT (取得するカラム) FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON (結合条件);
結合条件のところで同じ値があるかどうかを調べるそれぞれのテーブルのカラムを指定します。取得するデータは2つのテーブルのカラムが混合したものとなりますので、取得するカラムの指定には「カラム名」ではなくどのテーブルのカラムなのかを表すために「テーブル名.カラム名」の形で指定します。
外部結合は内部結合の場合と似ているのですが、LEFT OUTER JOIN句を使用する場合は結合の対象となっているカラムの値が一致しているデータに加えて、カラムの値がFROMの後に書かれたテーブルにしかなかった場合でもデータとして取得します。
それでは簡単な例を使ってどのように使用するのかについて確認します。社員の一覧が登録された「staff」テーブルと、部署が登録された「dept」テーブルを用意しました。
create table staff(id integer, name text, deptid integer);
create table dept(id integer, name text);
staffテーブルには次のようなデータが入っています。
deptテーブルには次のようなデータが入っています。
では次のように外部結合し、データを取得してみます。
select * from staff left outer join dept on staff.deptid = dept.id;
内部結合と同じく外部結合した場合も取得できるデータは2つのテーブルのデータが結合されたものになります。どのようなルールに従って取得するデータが結合されているのかについてはこの後説明します。
先ほどのサンプルを元にどのようにテーブルが外部結合(LEFT OUTER JOIN句)されるのかについて確認していきます。まずSELECT文の最初に記述されたテーブルの1つ目のデータに対して結合が行われます。
id name deptid -------------------- 1 Yamada 1
結合条件は「staff.deptid = dept.id」です。このデータの「deptid」の値は「1」ですので、deptテーブルのデータの中で「id」カラムの値が「1」のものがあるかどうかを調べます。今回は1つ見つかりました。
id name ------------ 1 Sales
見つかった場合にはこの2つのテーブルのデータを結合して1つのデータとして返します。
id name deptid id name ------------------------------- 1 Yamada 1 1 Sales
staffテーブルのデータを順番に同じように結合していきます。
id name deptid id name ----------------------------------- 1 Yamada 1 1 Sales 2 Suzuki 3 3 Publicity 3 Kuroda 2 2 Technical 4 Ishida 1 1 Sales
もし結合条件に一致しないデータがあった場合、外部結合(LEFT OUTER JOIN句)の場合にはFROMの後に書かれたテーブルのデータだけを取得します。下記のデータの場合、「deptid」カラムの値が「5」ですが、deptテーブルのデータの中には「id」カラムの値が「5」のものがありませんのでstaffテーブルの値だけを取得します。
id name deptid -------------------- 5 Takahashi 5
staffテーブルのデータを最後まで同じ手順で結合したら終了です。最終的に取得できるデータは次の通りです。
id name deptid id name ----------------------------------- 1 Yamada 1 1 Sales 2 Suzuki 3 3 Publicity 3 Kuroda 2 2 Technical 4 Ishida 1 1 Sales 5 Takahashi 5 6 Eguchi 2 2 Technical 7 Adachi 1 1 Sales
これは先ほど実際に試した結果と一致します。内部結合の場合と違い外部結合の場合は結合条件に一致しないデータでも取得することに注意して下さい。
比較元のカラムの値が比較崎の複数のデータと一致する場合があります。その場合は一致する全てのデータに対してそれぞれ結合が行われデータを取得します。これは内部結合の場合と同じですので詳細は「結合が複数のデータで一致する場合」を参照されて下さい。
外部結合の場合も実際に試しておきます。
select * from dept left outer join staff on dept.id = staff.deptid;
外部結合ですので結合条件に一致しないデータも取得する点は異なりますが、その他については内部結合の場合と同じです。
取得するデータのカラム指定方法は内部結合の場合と同じです。詳細は「取得するデータのカラム指定方法」を参照されて下さい。2つのテーブルで同じカラム名がある場合は「テーブル名.カラム名」のように指定し、どちらかのテーブルにしかないカラム名の場合はテーブル名を省略して「カラム名」で指定します。
外部結合の場合も実際に試しておきます。
select staff.id, staff.name, dept.name from staff left outer join dept on staff.deptid = dept.id;
カラムを指定して外部結合したテーブルからデータを取得することができました。
( Written by Tatsuo Ikura )
DBOnline