正規化されているテーブルは、データを表示するときにテーブルを結合して使用する場合が多いです。
下で示されているように、結合式が無ければ、レコード相互のすべての組み合わせが析出されます。結合のタイプにより、条件式をつければ、条件にあったデータを取り出せます。
ここでは、好きな色情報を、個人情報テーブルと色情報テーブルに格納し、二つのテーブルを結合してみることにします。
内部結合 [INNER JOIN]
内部結合は、結合条件式により、指定したカラムの値が一致するデータだけを取り出します。
select target_table.column name,...from target_table inner join joinning_table on joinning_condition;
target_tableにjoinning_tableをjoinning_condition条件で結合させて表示する。$ sqlite3 join;
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
---- 好きな色の個人情報テーブル生成
sqlite> create table favorite_color (name_id integer primary key, name text, bone integer check(bone between 1000 and 9999), favorite integer);
---- 色情報テーブル生成、データ格納
sqlite> create table color (color_id integer primary key, color_name text);
sqlite> insert into color values(null, 'white');
sqlite> insert into color values(null, 'yellow');
sqlite> insert into color values(null, 'green');
sqlite> insert into color values(null, 'blue');
sqlite> insert into color values(null, 'pink');
sqlite> insert into color values(null, 'brown');
sqlite> insert into color values(null, 'black');
sqlite> .mode column
sqlite> select * from color;
color_id color_name
-------- ----------
1 white
2 yellow
3 green
4 blue
5 pink
6 brown
7 black
---- 好きな色個人情報格納
sqlite> insert into favorite_color values(null, 'takai', 1956, 3);
sqlite> insert into favorite_color values(null, 'akiyama', 1945, 2);
sqlite> insert into favorite_color values(null, 'yamada', 2000, 4);
sqlite> insert into favorite_color values(null, 'sasaki', 2005, 1);
sqlite> insert into favorite_color values(null, 'sasaki', 2005, 4);
sqlite> insert into favorite_color values(null, 'kato', 2011, 6);
sqlite> insert into favorite_color values(null, 'nakata', 2020, null);
sqlite> select * from favorite_color;
name_id name bone favorite
------- ------- ---- --------
1 takai 1956 3
2 akiyama 1945 2
3 yamada 2000 4
4 sasaki 2005 1
5 sasaki 2005 4
6 kato 2011 6
7 nakata 2020
---- 内部結合する（結合条件式をつけない）
sqlite> select * from favorite_color
...> inner join color;
name_id name bone favorite color_id color_name
------- ------- ---- -------- -------- ----------
1 takai 1956 3 1 white
1 takai 1956 3 2 yellow
1 takai 1956 3 3 green
1 takai 1956 3 4 blue
1 takai 1956 3 5 pink
1 takai 1956 3 6 brown
1 takai 1956 3 7 black
2 akiyama 1945 2 1 white
2 akiyama 1945 2 2 yellow
2 akiyama 1945 2 3 green
2 akiyama 1945 2 4 blue
2 akiyama 1945 2 5 pink
2 akiyama 1945 2 6 brown
2 akiyama 1945 2 7 black
3 yamada 2000 4 1 white
3 yamada 2000 4 2 yellow
3 yamada 2000 4 3 green
3 yamada 2000 4 4 blue
3 yamada 2000 4 5 pink
3 yamada 2000 4 6 brown
3 yamada 2000 4 7 black
4 sasaki 2005 1 1 white
4 sasaki 2005 1 2 yellow
4 sasaki 2005 1 3 green
4 sasaki 2005 1 4 blue
4 sasaki 2005 1 5 pink
4 sasaki 2005 1 6 brown
4 sasaki 2005 1 7 black
5 sasaki 2005 4 1 white
5 sasaki 2005 4 2 yellow
5 sasaki 2005 4 3 green
5 sasaki 2005 4 4 blue
5 sasaki 2005 4 5 pink
5 sasaki 2005 4 6 brown
5 sasaki 2005 4 7 black
6 kato 2011 6 1 white
6 kato 2011 6 2 yellow
6 kato 2011 6 3 green
6 kato 2011 6 4 blue
6 kato 2011 6 5 pink
6 kato 2011 6 6 brown
6 kato 2011 6 7 black
7 nakata 2020 1 white
7 nakata 2020 2 yellow
7 nakata 2020 3 green
7 nakata 2020 4 blue
7 nakata 2020 5 pink
7 nakata 2020 6 brown
7 nakata 2020 7 black
---- 内部結合する（結合条件式をつける）
sqlite> select * from favorite_color
...> inner join color
...> on favorite=color_id;
name_id name bone favorite color_id color_name
------- ------- ---- -------- -------- ----------
1 takai 1956 3 3 green
2 akiyama 1945 2 2 yellow
3 yamada 2000 4 4 blue
4 sasaki 2005 1 1 white
5 sasaki 2005 4 4 blue
6 kato 2011 6 6 brown
sqlite> select name_id, name, favorite, color_name from favorite_color
...> inner join color
...> on favorite=color_id;
name_id name favorite color_name
------- ------- -------- ----------
1 takai 3 green
2 akiyama 2 yellow
3 yamada 4 blue
4 sasaki 1 white
5 sasaki 4 blue
6 kato 6 brown結合条件式USING(列名)で式を簡略化する [USING]
USING (共通のコラム名)
結合条件式 ON のなかで、table名.共通のコラム名 = another table名.共通のコラム名である場合
using 共通のコラム名 とすることが出来る。sqlite> select * from color;
color_id color_name
-------- ----------
1 white
2 yellow
3 green
4 blue
5 pink
6 brown
7 black
---- 車のカラー情報テーブルを定義、格納する
sqlite> create table car (id integer primary key, car_name, color_id);
sqlite> insert into car values(null, 'tank', 1);
sqlite> insert into car values(null, 'karola', 4);
sqlite> insert into car values(null, 'vitze', 7);
sqlite> insert into car values(null, 'noa', 1);
sqlite> select * from car;
id car_name color_id
-- -------- --------
1 tank 1
2 karola 4
3 vitze 7
4 noa 1
---- on による結合条件式
sqlite> select car_name, car.color_id, color.color_name from car
...> inner join color
...> on car.color_id=color.color_id;
car_name color_id color_name
-------- -------- ----------
tank 1 white
karola 4 blue
vitze 7 black
noa 1 white
---- sql文の中で唯一のコラム名であれば、table name.column name のテーブル名は省略できる
sqlite> select car_name, car.color_id, color_name from car
...> inner join color
...> on car.color_id=color.color_id;
car_name color_id color_name
-------- -------- ----------
tank 1 white
karola 4 blue
vitze 7 black
noa 1 white
---- on 結合式 を using 結合式で記述する
sqlite> select car_name, car.color_id, color_name from car
...> inner join color
...> using (color_id); -- color テーブルとcarテーブルに共通のcolor_id列がある
car_name color_id color_name
-------- -------- ----------
tank 1 white
karola 4 blue
vitze 7 black
noa 1 white外部結合 [OUTER JOIN]
外部結合は、結合条件式により、指定したカラムの値が一致するデータであるかにかかわらず、とりだします。
select target_table.column name,...from target_table outer join joinning_table on joinning_condition;
target_tableにjoinning_tableをjoinning_condition条件で結合させて表示する。SQL 標準では LEFT、RIGHT、FULL の 3 種類の OUTER JOIN が定義されていますが、SQLite はLEFT OUTER JOINのみをサポートしています。この場合は、target_table の左に、joinning table が析出される。結合条件式が真であるレコード以外に、真とならないレコードも付け加わる。その場合は、target table 側のフィールドは、null として表示される。
---- 外部結合する（結合条件式をつけない）
sqlite> select * from favorite_color
...> left outer join color; -- cross join として析出される
name_id name bone favorite color_id color_name
------- ------- ---- -------- -------- ----------
1 takai 1956 3 1 white
1 takai 1956 3 2 yellow
1 takai 1956 3 3 green
1 takai 1956 3 4 blue
1 takai 1956 3 5 pink
1 takai 1956 3 6 brown
1 takai 1956 3 7 black
2 akiyama 1945 2 1 white
2 akiyama 1945 2 2 yellow
2 akiyama 1945 2 3 green
2 akiyama 1945 2 4 blue
2 akiyama 1945 2 5 pink
2 akiyama 1945 2 6 brown
2 akiyama 1945 2 7 black
3 yamada 2000 4 1 white
3 yamada 2000 4 2 yellow
3 yamada 2000 4 3 green
3 yamada 2000 4 4 blue
3 yamada 2000 4 5 pink
3 yamada 2000 4 6 brown
3 yamada 2000 4 7 black
4 sasaki 2005 1 1 white
4 sasaki 2005 1 2 yellow
4 sasaki 2005 1 3 green
4 sasaki 2005 1 4 blue
4 sasaki 2005 1 5 pink
4 sasaki 2005 1 6 brown
4 sasaki 2005 1 7 black
5 sasaki 2005 4 1 white
5 sasaki 2005 4 2 yellow
5 sasaki 2005 4 3 green
5 sasaki 2005 4 4 blue
5 sasaki 2005 4 5 pink
5 sasaki 2005 4 6 brown
5 sasaki 2005 4 7 black
6 kato 2011 6 1 white
6 kato 2011 6 2 yellow
6 kato 2011 6 3 green
6 kato 2011 6 4 blue
6 kato 2011 6 5 pink
6 kato 2011 6 6 brown
6 kato 2011 6 7 black
7 nakata 2020 1 white
7 nakata 2020 2 yellow
7 nakata 2020 3 green
7 nakata 2020 4 blue
7 nakata 2020 5 pink
7 nakata 2020 6 brown
7 nakata 2020 7 black
---- 外部結合する（結合条件式をつける）
sqlite> select name_id, name, favorite, color_name from favorite_color
...> left outer join color
...> on favorite=color_id;
name_id name favorite color_name
------- ------- -------- ----------
1 takai 3 green
2 akiyama 2 yellow
3 yamada 4 blue
4 sasaki 1 white
5 sasaki 4 blue
6 kato 6 brown
7 nakata -- 条件式に一致しなくても表示される交差結合 [CLOSS JOIN]
交差結合は、2 つのテーブルのデータの全ての組み合わせを取得するものです。
select target_table.column name,...from target_table closs join joinning_table on joinning_condition;
target_tableにjoinning_tableをjoinning_condition条件で結合させて表示する。以下は、favorite_colorテーブルとcolorテーブルの組み合わせでchois_clothesテーブルを作成しています。
sqlite> select * from favorite_color closs join color;
name_id name bone favorite color_id color_name
------- ------- ---- -------- -------- ----------
1 takai 1956 3 1 white
1 takai 1956 3 2 yellow
1 takai 1956 3 3 green
1 takai 1956 3 4 blue
1 takai 1956 3 5 pink
1 takai 1956 3 6 brown
1 takai 1956 3 7 black
2 akiyama 1945 2 1 white
2 akiyama 1945 2 2 yellow
2 akiyama 1945 2 3 green
2 akiyama 1945 2 4 blue
2 akiyama 1945 2 5 pink
2 akiyama 1945 2 6 brown
2 akiyama 1945 2 7 black
3 yamada 2000 4 1 white
3 yamada 2000 4 2 yellow
3 yamada 2000 4 3 green
3 yamada 2000 4 4 blue
3 yamada 2000 4 5 pink
3 yamada 2000 4 6 brown
3 yamada 2000 4 7 black
4 sasaki 2005 1 1 white
4 sasaki 2005 1 2 yellow
4 sasaki 2005 1 3 green
4 sasaki 2005 1 4 blue
4 sasaki 2005 1 5 pink
4 sasaki 2005 1 6 brown
4 sasaki 2005 1 7 black
5 sasaki 2005 4 1 white
5 sasaki 2005 4 2 yellow
5 sasaki 2005 4 3 green
5 sasaki 2005 4 4 blue
5 sasaki 2005 4 5 pink
5 sasaki 2005 4 6 brown
5 sasaki 2005 4 7 black
6 kato 2011 6 1 white
6 kato 2011 6 2 yellow
6 kato 2011 6 3 green
6 kato 2011 6 4 blue
6 kato 2011 6 5 pink
6 kato 2011 6 6 brown
6 kato 2011 6 7 black
7 nakata 2020 1 white
7 nakata 2020 2 yellow
7 nakata 2020 3 green
7 nakata 2020 4 blue
7 nakata 2020 5 pink
7 nakata 2020 6 brown
7 nakata 2020 7 black
---- name_id=1 とする条件を絞って表示する
sqlite> select name_id, name, color_id, color_name from favorite_color
...> closs join color
...> on name_id=1;
name_id name color_id color_name
------- ----- -------- ----------
1 takai 1 white
1 takai 2 yellow
1 takai 3 green
1 takai 4 blue
1 takai 5 pink
1 takai 6 brown
1 takai 7 black
---- closs joinを使って chois_cloths テーブルを作成する
sqlite> .mode column
sqlite> select * from color;
color_id color_name
-------- ----------
1 white
2 yellow
3 green
4 blue
5 pink
6 brown
7 black
sqlite> select * from favorite_color ;
name_id name bone favorite
------- ------- ---- --------
1 takai 1956 3
2 akiyama 1945 2
3 yamada 2000 4
4 sasaki 2005 1
5 sasaki 2005 4
6 kato 2011 6
7 nakata 2020
---- favorite_colorテーブルとcolorテーブルの組み合わせでchois_clothesテーブルを作成する
sqlite> create table chois_clothes
...> as select
...> name_id, name, color_id, color_name from favorite_color
...> cross join color
...> on name='takai' or name='akiyama';
---- 組み合わせを表示する
sqlite> select * from chois_clothes;
name_id name color_id color_name
------- ------- -------- ----------
1 takai 1 white
1 takai 2 yellow
1 takai 3 green
1 takai 4 blue
1 takai 5 pink
1 takai 6 brown
1 takai 7 black
2 akiyama 1 white
2 akiyama 2 yellow
2 akiyama 3 green
2 akiyama 4 blue
2 akiyama 5 pink
2 akiyama 6 brown
2 akiyama 7 black
---- 新たにfavorite_count列を追加する
sqlite> alter table chois_clothes add favorite_count integer;
---- chois_clothesテーブルのメタ情報を確認する
sqlite> .schema chois_clothes
CREATE TABLE chois_clothes(
name_id INT,
name TEXT,
color_id INT,
color_name TEXT
, favorite_count integer);
---- favorite_count列 にデータを挿入する
sqlite> update chois_clothes set favorite_count=1 where name='takai' and color_name='white';
sqlite> update chois_clothes set favorite_count=1 where name='akiyama' and color_name='green';
---- chois_clothesテーブルデータを表示する
sqlite> select * from chois_clothes;
name_id name color_id color_name favorite_count
------- ------- -------- ---------- --------------
1 takai 1 white 1
1 takai 2 yellow
1 takai 3 green
1 takai 4 blue
1 takai 5 pink
1 takai 6 brown
1 takai 7 black
2 akiyama 1 white
2 akiyama 2 yellow
2 akiyama 3 green 1
2 akiyama 4 blue
2 akiyama 5 pink
2 akiyama 6 brown
2 akiyama 7 black自己結合
自己結合は、同じテーブルの中で、同一の列をそれぞれ別名をつけて結合することです。
SELECT column_name FROM table_name AS alias1
INNER JOIN || OUTER LEFT JOIN || CLOSS JOIN
alias AS alias2
[ON condition];---- 自分より早く生まれた人をカウントする ----
sqlite> select * from favorite_color;
name_id name bone favorite
------- ------- ---- --------
1 takai 1956 3
2 akiyama 1945 2
3 yamada 2000 4
4 sasaki 2005 1
5 sasaki 2005 4
6 kato 2011 6
7 nakata 2020
---- 内部結合のすべての組み合わせを表示
sqlite> select * from favorite_color as f1
...> inner join favorite_color as f2;
name_id name bone favorite name_id name bone favorite
------- ------- ---- -------- ------- ------- ---- --------
1 takai 1956 3 1 takai 1956 3
1 takai 1956 3 2 akiyama 1945 2
1 takai 1956 3 3 yamada 2000 4
1 takai 1956 3 4 sasaki 2005 1
1 takai 1956 3 5 sasaki 2005 4
1 takai 1956 3 6 kato 2011 6
1 takai 1956 3 7 nakata 2020
2 akiyama 1945 2 1 takai 1956 3
2 akiyama 1945 2 2 akiyama 1945 2
2 akiyama 1945 2 3 yamada 2000 4
2 akiyama 1945 2 4 sasaki 2005 1
2 akiyama 1945 2 5 sasaki 2005 4
2 akiyama 1945 2 6 kato 2011 6
2 akiyama 1945 2 7 nakata 2020
3 yamada 2000 4 1 takai 1956 3
3 yamada 2000 4 2 akiyama 1945 2
3 yamada 2000 4 3 yamada 2000 4
3 yamada 2000 4 4 sasaki 2005 1
3 yamada 2000 4 5 sasaki 2005 4
3 yamada 2000 4 6 kato 2011 6
3 yamada 2000 4 7 nakata 2020
4 sasaki 2005 1 1 takai 1956 3
4 sasaki 2005 1 2 akiyama 1945 2
4 sasaki 2005 1 3 yamada 2000 4
4 sasaki 2005 1 4 sasaki 2005 1
4 sasaki 2005 1 5 sasaki 2005 4
4 sasaki 2005 1 6 kato 2011 6
4 sasaki 2005 1 7 nakata 2020
5 sasaki 2005 4 1 takai 1956 3
5 sasaki 2005 4 2 akiyama 1945 2
5 sasaki 2005 4 3 yamada 2000 4
5 sasaki 2005 4 4 sasaki 2005 1
5 sasaki 2005 4 5 sasaki 2005 4
5 sasaki 2005 4 6 kato 2011 6
5 sasaki 2005 4 7 nakata 2020
6 kato 2011 6 1 takai 1956 3
6 kato 2011 6 2 akiyama 1945 2
6 kato 2011 6 3 yamada 2000 4
6 kato 2011 6 4 sasaki 2005 1
6 kato 2011 6 5 sasaki 2005 4
6 kato 2011 6 6 kato 2011 6
6 kato 2011 6 7 nakata 2020
7 nakata 2020 1 takai 1956 3
7 nakata 2020 2 akiyama 1945 2
7 nakata 2020 3 yamada 2000 4
7 nakata 2020 4 sasaki 2005 1
7 nakata 2020 5 sasaki 2005 4
7 nakata 2020 6 kato 2011 6
7 nakata 2020 7 nakata 2020
---- 析出条件をつける
sqlite> select f1.name_id, f1.name, count(*)-1 as less_bone_count from favorite_color as f1
...> inner join favorite_color as f2
...> on f1.bone >= f2.bone group by f1.name_id; -- count(*) されない場合は null となる。
name_id name less_bone_count
------- ------- ---------------
1 takai 1
2 akiyama 0
3 yamada 2
4 sasaki 4
5 sasaki 4
6 kato 5
7 nakata 6
2 つ以上の SELECT ステートメントの結果を結合する [UNION][UNION ALL]
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION/UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
各 SELECT が同じ数の列を選択し、同じ数の列式を、同じデータ型で、同じ順序にする必要があります---- 同じテーブル構造を持つcolorとcolor2のselectステートメント結果をまとめる
sqlite> select * from color;
color_id color_name
-------- ----------
1 white
2 yellow
3 green
4 blue
5 pink
6 brown
7 black
sqlite> create table color2 (color_id integer primary key, color_name text);
sqlite> insert into color2 values(null, 'gray');
sqlite> insert into color2 values(null, 'red');
sqlite> insert into color2 values(null, 'orange');
sqlite> insert into color2 values(4, 'blue');
sqlite> insert into color2 values(null, 'white');
sqlite> select * from color2;
color_id color_name
-------- ----------
1 gray
2 red
3 orange
4 blue -- colorテーブルデータと重複
5 white -- colorテーブルデータと重複
sqlite> select * from color
...> union
...> select * from color2;
color_id color_name
-------- ----------
1 gray
1 white
2 red
2 yellow
3 green
3 orange
4 blue
5 pink
5 white
6 brown
7 black
sqlite> select color_name from color
...> union
...> select color_name from color2;
color_name -- 重複は取り除かれる
----------
black
blue
brown
gray
green
orange
pink
red
white
yellowUNION ALL は単純にテーブルのデータを下につなげる。重複は許容されます。
sqlite> select * from color
...> union all
...> select * from color2;
color_id color_name
-------- ----------
1 white
2 yellow
3 green
4 blue
5 pink
6 brown
7 black
1 gray
2 red
3 orange
4 blue
5 white
