さて、今回はSQLの第6弾です。
今日はSQLの集合演算について説明していきます。
集合演算とは何か
集合演算とは、レコード同士を足したり引いたりするような、いわば「レコードの四則演算」です。
そして、このような集合演算を行う為の演算子のことを「集合演算子」と呼びます。
テーブルの足し算、UNION
まずは、Shohinテーブルとデータは同じで名前が違うShohin2テーブルを作成します。
create table Shohin2
(shohin_id char(4) not null,
shohin_mei varchar(100) not null,
shohin_bunrui varchar(32) not null,
hanbai_tanka integer,
shiire_tanka integer,
torokubi date,
primary key (shohin_id));
次に、データを登録していきます。
begin transaction;
insert into Shohin2 values('0001', 'Tシャツ', '衣服', 1000, 500, '2008-09-20');
insert into Shohin2 values('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11');
insert into Shohin2 values('0003', 'カッターシャツ', '衣服', 4000, 2800, null);
insert into Shohin2 values('0009', '手袋', '衣服', 800, 500, null);
insert into Shohin2 values('0010', 'やかん', 'キッチン用品', 2000, 1700, '2009-09-20');
commit;
確認。
-- 確認
select * from Shohin2;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2008-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0009 | 手袋 | 衣服 | 800 | 500 |
0010 | やかん | キッチン用品 | 2000 | 1700 | 2009-09-20
(5 行)
はい、それではここでUNIONを使って「Shohinテーブル」と「Shohin2テーブル」を結合してみます。
コードは以下のように書きます。
select shohin_id, shohin_mei
from Shohin
union
select shohin_id, shohin_mei
from Shohin2;
実行結果です。
shohin_id | shohin_mei
-----------+----------------
0006 | フォーク
0009 | 手袋
0005 | 圧力鍋
0007 | おろしがね
0008 | ボールペン
0002 | 穴あけパンチ
0001 | Tシャツ
0009 | 印鑑
0003 | カッターシャツ
0004 | 包丁
0010 | やかん
(11 行)
この結果は2つのテーブルに含まれていたレコードが、すべて網羅される形となります。
そして、UNIONに限らず集合演算子では重複は自動的に排除されます。
そして、集合演算を行うときには、以下のような3つほどの注意事項があります。
- 演算対象となるレコードの列数は同じであること
- 足し算の対象となるレコードの列のデータ型が一致していること
- SELECT文はどんなものでも指定してよいが、ORDER BY句は最後に一つだけ付けられること
さらに、UNIONでは、重複行を残して結果を表示することができます。
書き方はUNIONのあとにALLオプションを付けるだけです。
select shohin_id, shohin_mei
from Shohin
union all
select shohin_id, shohin_mei
from Shohin2;
実行結果です。
shohin_id | shohin_mei
-----------+----------------
0001 | Tシャツ
0002 | 穴あけパンチ
0003 | カッターシャツ
0004 | 包丁
0005 | 圧力鍋
0006 | フォーク
0007 | おろしがね
0008 | ボールペン
0009 | 印鑑
0001 | Tシャツ
0002 | 穴あけパンチ
0003 | カッターシャツ
0009 | 手袋
0010 | やかん
(14 行)
いくつかのレコードが重複していることを確認してください。
テーブルの共通部分の選択、INTERSECT
次は、2つのテーブルの共通部分を選択するINTERSECTを紹介します。
書き方はUNIONとまったく同じです。
select shohin_id, shohin_mei
from Shohin
intersect
select shohin_id, shohin_mei
from Shohin2
order by shohin_id;
実行結果です。
shohin_id | shohin_mei
-----------+----------------
0001 | Tシャツ
0002 | 穴あけパンチ
0003 | カッターシャツ
(3 行)
はい。2つのテーブルの共通部分のみを表示することができました。
レコードの引き算、EXCEPT
最後の集合演算子はレコードの引き算を行うEXCEPTです。
これも書き方はUNIONと同じです。
select shohin_id, shohin_mei
from Shohin
except
select shohin_id, shohin_mei
from Shohin2
order by shohin_id;
実行結果です。
shohin_id | shohin_mei
-----------+------------
0004 | 包丁
0005 | 圧力鍋
0006 | フォーク
0007 | おろしがね
0008 | ボールペン
0009 | 印鑑
(6 行)
実行結果では、ShohinテーブルからShohin2テーブルのレコードを引いた残りが選択されています。
逆に、Shohin2テーブルからShohinテーブルのレコードを引きたい場合には、ShohinとShohin2を入れ替えて記述すれば可能です。
テーブルの結合について
さて、続いて結合の演算を説明していきます。
前回までのUNIONやINTERSECTでは、行方向の演算でした。
簡単に言うと、これらを使うことで行が増えたり、減ったりするということです。
しかし、結合の演算は「列を増やす」操作になります。
そして、この結合には「内部結合」と「外部結合」の2種類があります。
では、それぞれ見ていきましょう。
内部結合、INNER JOIN
まずは、内部結合の「INNER JOIN」です。サンプルに使うテーブルはShohinテーブルとTenpoShohinテーブルです。
この2つのテーブルを確認しておきましょう。
まず、Shohinテーブルです。
select * from Shohin;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20
0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15
0006 | フォーク | キッチン用品 | 500 | | 2009-09-20
0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28
0008 | ボールペン | 事務用品 | 100 | | 2009-11-11
0009 | 印鑑 | 事務用品 | 95 | 10 | 2009-11-30
(9 行)
次にTenpoShohinテーブルを作成します。
create table TenpoShohin
(tenpo_id char(4) not null,
tenpo_mei varchar(200) not null,
shohin_id char(4) not null,
suryo integer not null,
primary key (tenpo_id, shohin_id));
データを登録していきます。
begin transaction;
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000A', '東京', '0001', 30);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000A', '東京', '0002', 50);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000A', '東京', '0003', 15);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000B', '名古屋', '0002', 30);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000B', '名古屋', '0003', 120);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000B', '名古屋', '0004', 20);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000B', '名古屋', '0006', 10);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000B', '名古屋', '0007', 40);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000C', '大阪', '0003', 20);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000C', '大阪', '0004', 50);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000C', '大阪', '0006', 90);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000C', '大阪', '0007', 70);
insert into TenpoShohin(tenpo_id, tenpo_mei, shohin_id, suryo) values('000D', '福岡', '0001', 100);
commit;
確認してみます。
select * from TenpoShohin;
tenpo_id | tenpo_mei | shohin_id | suryo
----------+-----------+-----------+-------
000A | 東京 | 0001 | 30
000A | 東京 | 0002 | 50
000A | 東京 | 0003 | 15
000B | 名古屋 | 0002 | 30
000B | 名古屋 | 0003 | 120
000B | 名古屋 | 0004 | 20
000B | 名古屋 | 0006 | 10
000B | 名古屋 | 0007 | 40
000C | 大阪 | 0003 | 20
000C | 大阪 | 0004 | 50
000C | 大阪 | 0006 | 90
000C | 大阪 | 0007 | 70
000D | 福岡 | 0001 | 100
(13 行)
それでは、Shohinテーブルから具体的な商品名(Shohin_mei)、販売単価(hanbai_tanka)の列を持ってきてTenpoShohinテーブルにくっつけてみます。
select TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
from TenpoShohin as TS inner join Shohin as S
on TS.shohin_id = S.Shohin_id;
実行結果です。
tenpo_id | tenpo_mei | shohin_id | shohin_mei | hanbai_tanka
----------+-----------+-----------+----------------+--------------
000A | 東京 | 0001 | Tシャツ | 1000
000A | 東京 | 0002 | 穴あけパンチ | 500
000A | 東京 | 0003 | カッターシャツ | 4000
000B | 名古屋 | 0002 | 穴あけパンチ | 500
000B | 名古屋 | 0003 | カッターシャツ | 4000
000B | 名古屋 | 0004 | 包丁 | 3000
000B | 名古屋 | 0006 | フォーク | 500
000B | 名古屋 | 0007 | おろしがね | 880
000C | 大阪 | 0003 | カッターシャツ | 4000
000C | 大阪 | 0004 | 包丁 | 3000
000C | 大阪 | 0006 | フォーク | 500
000C | 大阪 | 0007 | おろしがね | 880
000D | 福岡 | 0001 | Tシャツ | 1000
(13 行)
このINNER JOINはポイントがいくつかあります。
まず第一に、これまでFROM句に1つのテーブルしか書いていなかったのが、TenpoShohinとShohinテーブルの2つ書いていることです。
from TenpoShohin as TS inner join Shohin as S
これを可能にしているのが、INNER JOINです。
TSとSというのはテーブルの別名です。SELECT句でTenpoShohin.shohin_idのように書いても問題はありませんが、手間と読みやすさを意識するなら省略した書き方の方がよいと思います。
次に、ONのあとに記述されている結合条件です。
on TS.shohin_id = S.Shohin_id;
これは、2つのテーブルをくっつけるための結合キーを指定しています。
ここで言うと、shohin_idキーがそれに当たります。
最後に、SELECT句で指定する列です。
select TS.tenpo_id, TS.tenpo_mei, TS.shohin_id, S.shohin_mei, S.hanbai_tanka
<テーブル名>.<列名>という記述をすることで、どのテーブルからどの列持ってきているかをを明確にする役割があります。
外部結合、OUTER
外部結合も内部結合と基本的な使い方は変わりません。
しかし、出力される結果が異なります。
実際に見た方が早いので、実際にOUTER JOINを使って先ほどのTenpoShohinとShohinテーブルを外部結合してみましょう。
select TS.tenpo_id, TS.tenpo_mei, S.shohin_id, S.shohin_mei, S.hanbai_tanka
from TenpoShohin as TS right outer join Shohin as S
on TS.shohin_id = S.shohin_id;
では、結果を見てみます。
tenpo_id | tenpo_mei | shohin_id | shohin_mei | hanbai_tanka
----------+-----------+-----------+----------------+--------------
000A | 東京 | 0001 | Tシャツ | 1000
000A | 東京 | 0002 | 穴あけパンチ | 500
000A | 東京 | 0003 | カッターシャツ | 4000
000B | 名古屋 | 0002 | 穴あけパンチ | 500
000B | 名古屋 | 0003 | カッターシャツ | 4000
000B | 名古屋 | 0004 | 包丁 | 3000
000B | 名古屋 | 0006 | フォーク | 500
000B | 名古屋 | 0007 | おろしがね | 880
000C | 大阪 | 0003 | カッターシャツ | 4000
000C | 大阪 | 0004 | 包丁 | 3000
000C | 大阪 | 0006 | フォーク | 500
000C | 大阪 | 0007 | おろしがね | 880
000D | 福岡 | 0001 | Tシャツ | 1000
| | 0008 | ボールペン | 100
| | 0009 | 印鑑 | 95
| | 0005 | 圧力鍋 | 6800
(16 行)
内部結合の結果と比較してみましょう。
外部結合の場合は、
| | 0008 | ボールペン | 100
| | 0009 | 印鑑 | 95
| | 0005 | 圧力鍋 | 6800
(16 行)
この部分が追加されています。
これはどういうことかというと、外部結合は片方のテーブルを主(マスター)とし、そのマスターのテーブルの情報はすべて出力されるようになっています。
では、どちらのテーブルをマスターとするかを決めているのはどこかというと、
from TenpoShohin as TS right outer join Shohin as S
このFROM句の文です。
注目は「RIGHT」という記述。これはこの文の右側。つまりShohinテーブルの方をマスターのテーブルとすることを示しています。
また、この「RIGHT」の部分は「LEFT」と書いてもよいです。
その場合は、Shohinの記述を左側に持ってくる必要があります。
基本的にはどちらで書いてもいいですが、一般的には「LEFT」の方で書くことが多いようです。
コメント