SQLite | データの結合 | 同じテーブルを結合する（SELF JOIN）

Tags:

• SQLite
• データベース

内部結合や外部結合では通常複数のテーブルを結合しますが、テーブルをそれ自身と結合することもできます。これを自己結合（SELF JOIN）と呼びます。

自己結合の使い方

自己結合は同じテーブルに対して内部結合または外部結合を行います。同じテーブルを2回使用するため、それぞれに別の別名を指定します。

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

FROMの後とJOINの後のテーブルは同じです。別名によって、結合条件や結果で重複する列名を区別できます。

–

例で使用するテーブルを作成します。

create table employee (id integer, name text, boss_id integer);

sqlite> create table employee (id integer, name text, boss_id integer);
sqlite> 

次のデータを追加します。

insert into employee values (1, 'devkuma', 3);
insert into employee values (2, 'kimkc', 4);
insert into employee values (3, 'araikuma', 5);
insert into employee values (4, 'happykuma', 3);
insert into employee values (5, 'raccoon', 0);

sqlite> insert into employee values (1, 'devkuma', 3);
sqlite> insert into employee values (2, 'kimkc', 4);
sqlite> insert into employee values (3, 'araikuma', 5);
sqlite> insert into employee values (4, 'happykuma', 3);
sqlite> insert into employee values (5, 'raccoon', 0);
sqlite> 

employeeテーブルには、識別番号のid、従業員名のname、上司の識別番号のboss_idがあります。上司も同じテーブルに保存され、boss_idは別の行のidを参照します。

自己結合で上司の名前を取得します。

select staff.id, staff.name, boss.name from employee staff
  left outer join employee boss
  on staff.boss_id = boss.id;

sqlite> select staff.id, staff.name, boss.name from employee staff
   ...>   left outer join employee boss
   ...>   on staff.boss_id = boss.id;
id          name        name      
----------  ----------  ----------
1           devkuma     araikuma  
2           kimkc       happykuma 
3           araikuma    raccoon   
4           happykuma   araikuma  
5           raccoon               
sqlite> 

AS句で結果列に別名を付けることもできます。詳細は取得したデータの列に別名を設定する（AS句）を参照してください。

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

sqlite> select staff.id, staff.name, boss.name as bossname
   ...>   from employee staff
   ...>   left outer join employee boss
   ...>   on staff.boss_id = boss.id;
id          name        bossname  
----------  ----------  ----------
1           devkuma     araikuma  
2           kimkc       happykuma 
3           araikuma    raccoon   
4           happykuma   araikuma  
5           raccoon               
sqlite> 

この例は外部結合を使いましたが、同じ手法を内部結合にも適用できます。