SQLite | データの結合 | 外部結合（OUTER JOIN）
外部結合は一致する行に加えて、一方にしかない行も取得します。ここではLEFT OUTER JOINを説明します。
外部結合
外部結合は指定カラムの値を照合し、内部結合とは異なり一致しない行も返します。
図では左テーブルの部門IDと右テーブルのIDを照合します。
右側に一致するIDがなくても左側の行を取得します。
注意
LEFT OUTER JOINは左側、RIGHT OUTER JOINは右側、FULL OUTER JOINは両側の一致しない行を保持します。
この例で使用しているSQLiteはLEFT OUTER JOINだけをサポートし、RIGHTとFULL OUTER JOINはエラーになります。
LEFT OUTER JOINの使い方
SELECTとLEFT OUTER JOINを組み合わせます。構文は次のとおりです。
SELECT テーブル名. カラム名, ... FROM テーブル名1
LEFT OUTER JOIN テーブル名2
ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
簡略化すると次の形式です。
SELECT (取得カラム) FROM テーブル名1
LEFT OUTER JOIN テーブル名2 ON (結合条件);
必要に応じて取得カラムをテーブル名.カラム名で修飾します。
結合条件はテーブル1.カラム1 = テーブル2.カラム2と記述します。
左外部結合は一致する行に加え、FROM直後のテーブルにしかない行も返します。
–
employeeとdeptテーブルを作成し、サンプルデータを追加します。
create table employee (id integer, name text, dept_id integer);
insert into employee values (1, 'dekuma', 1);
insert into employee values (2, 'kimkc', 3);
insert into employee values (3, 'araikuma', 1);
insert into employee values (4, 'happykuma', 2);
insert into employee values (5, 'mykuma', 4);
insert into employee values (6, 'raccoon', 3);
sqlite> create table employee (id integer, name text, dept_id integer);
sqlite>
sqlite> insert into employee values (1, 'dekuma', 1);
sqlite> insert into employee values (2, 'kimkc', 3);
sqlite> insert into employee values (3, 'araikuma', 1);
sqlite> insert into employee values (4, 'happykuma', 2);
sqlite> insert into employee values (5, 'mykuma', 4);
sqlite> insert into employee values (6, 'raccoon', 3);
sqlite>
create table dept (id integer, name text);
insert into dept values (1, 'Sales');
insert into dept values (2, 'Manage');
insert into dept values (3, 'Dev');
sqlite> create table dept (id integer, name text);
sqlite>
sqlite> insert into dept values (1, 'Sales');
sqlite> insert into dept values (2, 'Manage');
sqlite> insert into dept values (3, 'Dev');
sqlite>
–
まずemployeeにdeptを左外部結合します。
select * from employee left outer join dept on employee.dept_id = dept.id;
sqlite> select * from employee left outer join dept on employee.dept_id = dept.id;
id name dept_id id name
---------- ---------- ---------- ---------- ----------
1 dekuma 1 1 Sales
2 kimkc 3 3 Dev
3 araikuma 1 1 Sales
4 happykuma 2 2 Manage
5 mykuma 4
6 raccoon 3 3 Dev
sqlite>
外部結合ではFROM直後のテーブルの不一致行を保持するため、テーブル順で結果が変わります。順序を逆にします。
select * from dept left outer join employee on dept.id = employee.dept_id;
sqlite> select * from employee left outer join employee on dept.id = employee.dept_id;
id name id name dept_id
---------- ---------- ---------- ---------- ----------
1 Sales 1 dekuma 1
1 Sales 3 araikuma 1
2 Manage 4 happykuma 2
3 Dev 2 kimkc 3
3 Dev 6 raccoon 3
sqlite>
deptだけにある行がないため、結果は内部結合と同じです。
取得するカラムを指定する
カラム指定は内部結合と同じです。両テーブルにある名前は修飾し、一方だけならテーブル名を省略できます。
外部結合でもカラムを指定します。
select employee.id, employee.name, dept.name from employee
left outer join dept on employee.dept_id = dept.id;
sqlite> select employee.id, employee.name, dept.name from employee
...> left outer join dept on employee.dept_id = dept.id;
id name name
---------- ---------- ----------
1 dekuma Sales
2 kimkc Dev
3 araikuma Sales
4 happykuma Manage
5 mykuma
6 raccoon Dev
sqlite>