テーブルを結合について
初めにデータベースに接続
c:\xampp\sqlite>sqlite3 myfriend.sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> .mode csv
sqlite> .mode column
sqlite>
■INNER JOIN句
結合条件のところで同じ値があるかどうかを調べるそれぞれのテーブルのカラムを指定します。
sqlite> select * from staff;
id name deptid
---------- ---------- ----------
1 Yamada 1
2 Suzuki 3
3 Kuroda 2
4 Ishida 1
5 Takahashi 5
6 Eguchi 2
7 Adachi 1
sqlite> select * from dept;
id name
---------- ----------
1 Sales
2 Technical
3 Publicity
4 Operation
deptid＝idで2つのテーブルを内部結合
sqlite> select * from staff inner join dept on staff.deptid = dept.id;
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
6 Eguchi 2 2 Technical
7 Adachi 1 1 Sales
sqlite>
結合が複数のデータで一致する場合
一致しない4 Operationは表示しない。
sqlite> select * from dept inner join staff on dept.id = staff.deptid;
id name id name deptid
---------- ---------- ---------- ---------- ----------
1 Sales 1 Yamada 1
1 Sales 4 Ishida 1
1 Sales 7 Adachi 1
2 Technical 3 Kuroda 2
2 Technical 6 Eguchi 2
3 Publicity 2 Suzuki 3
sqlite>
■取得するデータのカラム指定方法
SELECT文では「SELECT カラム名1, カラム名2, ・・・」に指示するが、結合の場合では「テーブル名.カラム名」に指示する。
sqlite> select staff.id, staff.name, dept.name from staff
...> inner join dept on staff.deptid = dept.id;
id name name
---------- ---------- ----------
1 Yamada Sales
2 Suzuki Publicity
3 Kuroda Technical
4 Ishida Sales
6 Eguchi Technical
7 Adachi Sales
■LEFT OUTER JOIN句
外部結合→指定したそれぞれのテーブルのカラムの値が一致するデータだけではなく、どちらかのテーブルにだけデータがある場合も取得する。
sqlite> select * from staff left outer join dept on staff.deptid = dept.id;
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
結合が複数のデータで一致する場合
sqlite> select * from dept left outer join staff on dept.id = staff.deptid;
id name id name deptid
---------- ---------- ---------- ---------- ----------
1 Sales 1 Yamada 1
1 Sales 4 Ishida 1
1 Sales 7 Adachi 1
2 Technical 3 Kuroda 2
2 Technical 6 Eguchi 2
3 Publicity 2 Suzuki 3
4 Operation
sqlite>
■CROSS JOIN句
交差結合→2つのテーブルのデータの全ての組み合わせを取得する方法
sqlite> select * from product3;
id name colorid
---------- ---------- ----------
1 Tablet 1
2 Printer 3
3 Mobile Pho 2
4 Mouse 3
sqlite> select * from color;
id name
---------- ----------
1 Black
2 Red
3 White
sqlite> select * from product3 cross join color;
id name colorid id name
---------- ---------- ---------- ---------- ----------
1 Tablet 1 1 Black
1 Tablet 1 2 Red
1 Tablet 1 3 White
2 Printer 3 1 Black
2 Printer 3 2 Red
2 Printer 3 3 White
3 Mobile Pho 2 1 Black
3 Mobile Pho 2 2 Red
3 Mobile Pho 2 3 White
4 Mouse 3 1 Black
4 Mouse 3 2 Red
4 Mouse 3 3 White
sqlite>
■NATURAL句
自然結合→NATURALキーワードを指定するとカラム名を指定しなくても二つのテーブルで同じ名前のカラム名を使って結合が行われます。(交差結合）
sqlite> select * from staff;
id staffname deptid
---------- ---------- ----------
1 Kaneko 1
2 Suzuki 2
3 Honda 4
4 Ueno 2
sqlite> select * from dept;
deptid deptname
---------- ----------
1 Sales
2 Technical
3 Operation
sqlite> select * from staff nature inner join dept;
id staffname deptid deptid deptname
---------- ---------- ---------- ---------- ----------
1 Kaneko 1 1 Sales
1 Kaneko 1 2 Technical
1 Kaneko 1 3 Operation
2 Suzuki 2 1 Sales
2 Suzuki 2 2 Technical
2 Suzuki 2 3 Operation
3 Honda 4 1 Sales
3 Honda 4 2 Technical
3 Honda 4 3 Operation
4 Ueno 2 1 Sales
4 Ueno 2 2 Technical
4 Ueno 2 3 Operation
sqlite>
内部結合にするには
sqlite> select * from staff inner join dept on staff.deptid = dept.deptid;
id staffname deptid deptid deptname
---------- ---------- ---------- ---------- ----------
1 Kaneko 1 1 Sales
2 Suzuki 2 2 Technical
4 Ueno 2 2 Technical
外部結合
sqlite> select * from staff natural left outer join dept;
id staffname deptid deptname
---------- ---------- ---------- ----------
1 Kaneko 1 Sales
2 Suzuki 2 Technical
3 Honda 4
4 Ueno 2 Technical
■自己結合
自分自身のテーブルと結合する場合
SELECT 別名1.カラム名, ...
FROM テーブル名 別名1 INNER JOIN テーブル名 別名2
ON 別名1.カラム名 = 別名2.カラム名;
sqlite> select * from staff;
id name bossid
---------- ---------- ----------
2 tanaka
3 endoh 2
4 inoue 3
5 suda
1 honda 3
sqlite> select * from staff person left outer join staff boss
...> on person.bossid = boss.id;
id name bossid id name bossid
---------- ---------- ---------- ---------- ---------- ----------
2 tanaka
3 endoh 2 2 tanaka
4 inoue 3 3 endoh 2
5 suda
1 honda 3 3 endoh 2
