同じテーブルを対象に結合する(自己結合)

広告

内部結合や外部結合では通常複数のテーブルを結合しますが、自分自身のテーブルと結合をすることも可能です。このような結合を自己結合と呼んでいます。ここでは自己結合の利用方法について解説します。

内部結合や外部結合を行う時に自分自身のテーブルと結合する自己結合をする場合の書式は次の通りです。 

SELECT 別名1.カラム名, ...
 FROM テーブル名 別名1 INNER JOIN テーブル名 別名2
 ON 別名1.カラム名 = 別名2.カラム名;

上記は内部結合の場合です。異なるテーブルを結合する場合と違い、FROMの後とJOINの後に記述するテーブルが同じテーブルとなります。ただ同じテーブルだと結合条件や取得するカラム名を記述する時に困りますので、同じテーブルではあるけれどそれぞれ別名を付けて区別できるようにします。(別名はどちらか1つのテーブルだけでも大丈夫なようです)。

それでは簡単な例を使ってどのように使用するのかについて確認します。次のようなテーブルを作成しました。 

create table staff(id integer, name text, bossid integer);

p5-1

staffテーブルには次のようなデータが入っています。

p5-2

staffテーブルでは識別番号を表す「ID」カラム、名前が格納されている「NAME」カラム、そしてその写真の上司の識別番号を格納する「BOSSID」の3つのカラムがあります。上司も同じstaffテーブルに格納されており、「BOSSID」カラムの値はstaffテーブルの他のデータの「ID」カラムの値が格納されています。

「BOSSID」の値を参照して上司の名前を結合したデータを取得したい場合、結合先のテーブルも同じstaffテーブルとなります。このような場合に自己結合を使用します。

では実際に同じテーブルを対象として外部結合し、データを取得してみます。 

select * from staff person left outer join staff boss 
  on person.bossid = boss.id;

p5-3

同じテーブルを対象として外部結合をすることができました。取得するカラムを指定したり、AS句を使ってカラムに別名を付けることもできます。 

select person.id, person.name, person.bossid, boss.name as bossname
  from staff person left outer join staff boss 
  on person.bossid = boss.id;

p5-4

今回は外部結合の場合で試してみましたが、内部結合の場合でも同じように使用することができます。

( Written by Tatsuo Ikura )