内部結合:INNER JOIN
内部結合とは
内部結合とは２つのテーブルで結合の対象となるカラムを指定し、それぞれに同じ値が格納されているデータを結合して取得するもの。
INNER JOIN句の使い方
書式
SELECT (取得するカラム) FROM テーブル名1 INNER JOIN テーブル名2 ON (結合条件);
内部結合の実践
2つのテーブルを用意する
1つめのテーブル
CREATE TABLE staff(id INTEGER, name TEXT, dept INTEGER);
INSERT INTO staff VALUES(1, 'Suzuki', 1);
INSERT INTO staff VALUES(2, 'Endou', 3);
INSERT INTO staff VALUES(3, 'Katou', 1);
INSERT INTO staff VALUES(4, 'Yamada', 2);
INSERT INTO staff VALUES(5, 'Takahashi', 4);
INSERT INTO staff VALUES(6, 'Honda', 3);
SELECT * FROM staff;
id name deptid
---------- ---------- ----------
1 Suzuki 1
2 Endou 3
3 Katou 1
4 Yamada 2
5 Takahashi 4
6 Honda 3
2つめのテーブル
CREATE TABLE dept(id INTEGER, name TEXT);
INSERT INTO dept(1, 'Sales');
INSERT INTO dept(2, 'Manage');
INSERT INTO dept(3, 'Dev');
SELECT * FROM dept;
id name
---------- ----------
1 Sales
2 Manage
2 Dev
テーブルの結合
staffテーブルにdeptテーブルを内部結合
SELECT * FROM staff INNER JOIN dept ON staff.deptid = dept.id;
id name deptid id name
---------- ---------- ---------- ---------- ----------
1 Suzuki 1 1 Sales
2 Endou 3 3 Dev
3 Katou 1 1 Sales
4 Yamada 2 2 Manage
6 Honda 3 3 Dev
deptテーブルにstaffテーブルを内部結合
SELECT * FROM dept INNER JOIN staff ON dept.id = staff.deptid;
id name id name deptid
---------- ---------- ---------- ---------- ----------
1 Sales 1 Suzuki 1
1 Sales 3 Katou 1
2 Manage 4 Yamada 2
取得するカラムを指定しての内部結合
SELECT staff.id, staff.name, dept.name FROM staff INNER JOIN dept ON staff.deptid = dept.id;
id name name
---------- ---------- ----------
1 Suzuki Sales
2 Endou Dev
3 Katou Sales
4 Yamada Manage
6 Honda Dev
テーブル名を省略した書式
次の条件を満たしている場合、テーブル名を省略してSELECT文を書くことができる。
- どちらか一方のテーブルにしか含まれていないこと
- テーブル名を省略してもどちらのテーブルのカラムかわかる場合
2つのテーブルに同じ名前のカラムがあるのにテーブル名を省略した場合は次のようなエラーが出る。
2つのテーブルに同じ名前のカラムがあるのにテーブル名を省略した場合
SELECT staff.id, name, deptid, dept.name FROM staff INNER JOIN dept ON staff.deptid = dept.id;
Error: ambiguous column name: name
外部結合:OUTER JOIN
外部結合とは
2つのテーブルを結合してデータを取得する方法の中で指定したそれぞれのテーブルのカラムの値が一致するデータにだけでなく、どちらかのテーブルにだけデータがある場合も合わせて取得する方法。
※内部結合と違いFROMの後のテーブル名を入れ替えると抽出される結果が異なります。
SQLite3での外部結合
一般的に外部結合には下記の3種類があります。
- 左外部結合：SELECT文の
FROM以降に記述された左側のテーブルにしかないデータも取得する方法。 - 右外部結合：SELECT文の
FROM以降に記述された右側のテーブルにしかないデータも取得する方法。 - 完全外部結合：SELECT文の
FROM以降に記述された左側のテーブルにしかないデータと右側のテーブルにしかないデータも取得する方法。
SQLite3では、左外部結合（LEFT OUTER JOIN）しかサポートされていない。
右外部結合（RIGHT OUTER JOIN）や完全外部結合（FULL OUTER JOIN）を行おうとすると、
Error:RIGHT and FULL OUTER JOINs are not currently supportedというエラーが出る。
OUTER JOIN句の使い方
SELECT (取得するカラム) FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON (結合条件);
外部結合の実践
2つのテーブルを用意する
1つめのテーブル
CREATE TABLE staff(id INTEGER, name TEXT, dept INTEGER);
INSERT INTO staff VALUES(1, 'Suzuki', 1);
INSERT INTO staff VALUES(2, 'Endou', 3);
INSERT INTO staff VALUES(3, 'Katou', 1);
INSERT INTO staff VALUES(4, 'Yamada', 2);
INSERT INTO staff VALUES(5, 'Takahashi', 4);
INSERT INTO staff VALUES(6, 'Honda', 3);
SELECT * FROM staff;
id name deptid
---------- ---------- ----------
1 Suzuki 1
2 Endou 3
3 Katou 1
4 Yamada 2
5 Takahashi 4
6 Honda 3
2つめのテーブル
CREATE TABLE dept(id INTEGER, name TEXT);
INSERT INTO dept(1, 'Sales');
INSERT INTO dept(2, 'Manage');
INSERT INTO dept(3, 'Dev');
SELECT * FROM dept;
id name
---------- ----------
1 Sales
2 Manage
2 Dev
テーブルの結合
SELECT * FROM staff LEFT OUTER JOIN dept ON staff.deptid = dept.id;
id name deptid id name
---------- ---------- ---------- ---------- ----------
1 Suzuki 1 1 Sales
2 Endou 3 3 Dev
3 Katou 1 1 Sales
4 Yamada 2 2 Manage
5 Takahashi 4
6 Honda 3 3 Dev
staffテーブルとdeptテーブルを逆にして外部結合
SELECT * FROM dept LEFT OUTER JOIN staff ON dept.id = staff.deptid;
id name id name deptid
---------- ---------- ---------- ---------- ----------
1 Sales 1 Suzuki 1
1 Sales 3 Katou 1
2 Manage 4 Yamada 2
3 Dev 2 Endou 3
3 Dev 6 Honda 3
取得するデータのカラムを指定する
SELECT staff.id, staff.name, dept.name FROM staff LEFT OUTER JOIN dept ON staff.deptid= dept.id;
id name name
---------- ---------- ----------
1 Suzuki Sales
2 Endou Dev
3 Katou Sales
4 Yamada Manage
5 Takahashi
6 Honda Dev
交差結合:CROSS JOIN
交差結合とは
2つのテーブルのデータの組み合わせを取得するもの。
SELECT文のFROM以降の左側のテーブルのデータ1つにつき右側のテーブルのデータを最初から最後まで1つずつ結合する。
左側のテーブルのデータ = n個、右側のテーブルのデータ = m個だった場合、交差結合したデータの個数がn✕m個となり、場合によっては非常に多くなるので注意が必要。
CROSS JOIN句の使い方
SELECT (取得するカラム) FROM テーブル名1 CROSS JOIN テーブル名2 ON (結合条件);
交差結合の実践
2つのテーブルを用意する
1つめのテーブル
CREATE TABLE product(id INTEGER, name TEXT);
INSERT INTO product VALUES(1, 'Desk');
INSERT INTO product VALUES(2, 'Light');
INSERT INTO product VALUES(3, 'Chair');
SELECT * FROM product;
id name
---------- ----------
1 Desk
2 Light
3 Chair
2つめのテーブル
CREATE TABLE color(id INTEGER, name TEXT);
INSERT INTO color VALUES(1, 'Red');
INSERT INTO color VALUES(2, 'Blue');
INSERT INTO color VALUES(3, 'White');
SELECT * FROM color;
id name
---------- ----------
1 Red
2 Blue
3 White
テーブルの結合
SELECT * FROM product CROSS JOIN color;
id name id name
---------- ---------- ---------- ----------
1 Desk 1 Red
1 Desk 2 Blue
1 Desk 3 White
2 Light 1 Red
2 Light 2 Blue
2 Light 3 White
3 Chair 1 Red
3 Chair 2 Blue
3 Chair 3 White
取得するデータのカラムを指定する
SELECT product.id, product.name, color.name FROM product CROSS JOIN color;
id name name
---------- ---------- ----------
1 Desk Red
1 Desk Blue
1 Desk White
2 Light Red
2 Light Blue
2 Light White
3 Chair Red
3 Chair Blue
3 Chair White
自然結合:NATURALキーワード
自然結合とは
内部結合や外部結合の場合、結合条件で指定したテーブルのカラムを比較してデータを結合した。
NATURALキーワードを指定するとカラム名を指定しなくても2つのテーブルで同名のカラムを使って結合を行える。
NATURALキーワードの使い方
- 内部結合の場合
SELECT (取得するカラム) FROM テーブル名1 NATURAL INNER JOIN テーブル名2;
- 外部結合の場合
SELECT (取得するカラム) FROM テーブル名1 NATURAL LEFT OUTER JOIN テーブル名2;
自然結合の実践
2つのテーブルを用意する
1つめのテーブル
CREATE TABLE staff(id INTEGER, name TEXT, dept INTEGER);
INSERT INTO staff VALUES(1, 'Suzuki', 1);
INSERT INTO staff VALUES(2, 'Endou', 3);
INSERT INTO staff VALUES(3, 'Katou', 1);
INSERT INTO staff VALUES(4, 'Yamada', 2);
INSERT INTO staff VALUES(5, 'Takahashi', 4);
INSERT INTO staff VALUES(6, 'Honda', 3);
SELECT * FROM staff;
id name deptid
---------- ---------- ----------
1 Suzuki 1
2 Endou 3
3 Katou 1
4 Yamada 2
5 Takahashi 4
6 Honda 3
2つめのテーブル
CREATE TABLE dept(deptid INTEGER, deptname TEXT);
INSERT INTO dept(1, 'Sales');
INSERT INTO dept(2, 'Manage');
INSERT INTO dept(3, 'Dev');
SELECT * FROM dept;
deptid deptname
---------- ----------
1 Sales
2 Manage
3 Dev
NATURALキーワードを用いた内部結合
SELECT * FROM staff NATURAL INNER JOIN dept;
id name deptid deptname
---------- ---------- ---------- ----------
1 Suzuki 1 Sales
2 Endou 3 Dev
3 Katou 1 Sales
4 Yamada 2 Manage
6 Honda 3 Dev
NATURALキーワードを用いた外部結合
SELECT * FROM staff NATURAL LEFT OUTER JOIN dept;
id name deptid deptname
---------- ---------- ---------- ----------
1 Suzuki 1 Sales
2 Endou 3 Dev
3 Katou 1 Sales
4 Yamada 2 Manage
5 Takahashi 4
6 Honda 3 Dev
自己結合
自己結合とは
内部結合や外部結合を行う時に自分自身のテーブルと結合すること。
自己結合では同じテーブルを使用するため、それぞれに別名を付けてSQLを記述する。
自己結合の使い方
SELECT 別名1.カラム名1, .... FROM テーブル名1 別名1 INNER JOIN テーブル名2 別名2 ON 別名1.カラム名1 = 別名2.カラム名2;
自己結合の実践
1つのテーブルを用意する
CREATE TABLE staff(id INTEGER, name TEXT, bossid INTEGER);
INSERT INTO staff VALUES(1, 'Yamada', 3);
INSERT INTO staff VALUES(2, 'Itou', 4);
INSERT INTO staff VALUES(3, 'Suzuki', 5);
INSERT INTO staff VALUES(4, 'Ueda', 3);
INSERT INTO staff VALUES(5, 'Kuroda', 0);
SELECT * FROM staff;
id name bossid
---------- ---------- ----------
1 Yamada 3
2 Itou 4
3 Suzuki 5
4 Ueda 3
5 Kuroda 0
テーブルに別名を付けて結合する
内部結合
SELECT person.id, person.name, boss.name FROM staff person INNER JOIN staff boss ON person.bossid = boss.id;
id name name
---------- ---------- ----------
1 Yamada Suzuki
2 Itou Ueda
3 Suzuki Kuroda
4 Ueda Suzuki
外部結合
SELECT person.id, person.name, boss.name FROM staff person LEFT OUTER JOIN staff boss ON person.bossid = boss.id;
id name name
---------- ---------- ----------
1 Yamada Suzuki
2 Itou Ueda
3 Suzuki Kuroda
4 Ueda Suzuki
5 Kuroda