■ はじめに
SQL で前から集合ってことは知っていたが、 「基本がわかるSQL入門」って本で、 ちゃんと書かれていたので、メモ。
* Snowflake については、以下の関連記事を参照のこと
Snowflake ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2024/09/12/003814
目次
【１】使用データ 【２】SQLで集合演算をする際の注意点 １）2つのテーブルのカラムが揃っている必要がある 【３】和（union） 【４】差（difference） 【５】共通（intersection） 【６】直積（direct product） 【７】選択（selection） 【８】射影（projection） 【９】結合（Join） 【１０】商（division）
【１】使用データ
* PostgreSQL17を想定
データ定義
CREATE TABLE member ( id INT, name VARCHAR(50), tel VARCHAR(30), updated_date DATE ); CREATE TABLE staff ( id INT, name VARCHAR(50), tel VARCHAR(30), updated_date DATE );
使用データ
INSERT INTO member (id,name,tel,updated_date) VALUES (1,'Mike','1234567890','2024-09-14'), (2,'Tom',NULL,'2024-09-14'), (3,'Smith','1111111111','2024-09-14'), (4,'Ken',NULL,'2024-09-14') ; INSERT INTO staff(id,name,tel,updated_date) VALUES (1,'Tom',NULL,'2024-10-15'), (2,'Ken',NULL,'2024-10-15'), (3,'Henry','66666666','2024-10-15'), (4,'George','3333333333333','2024-10-14'), (5,'Jack','22222222222','2024-10-14') ;
【２】SQLで集合演算をする際の注意点
１）2つのテーブルのカラムが揃っている必要がある
例
-- OK：id で揃っている SELECT id FROM member UNION ALL SELECT id FROM staff; -- NG：memberはid, nameだが、staffはid SELECT id, name FROM member UNION ALL SELECT id FROM staff;
【３】和（union）
* 2つ又は3つ以上の集合の全要素を集めること
１）SQL
* 和演算は、UNIONを使う * 重複行をそのまま残す場合は、UNION ALL => Union / Union All については、以下の関連記事を参照のこと
テーブルの結合 ～完全結合・Union / Union All ～
https://dk521123.hatenablog.com/entry/2010/03/28/162247
２）サンプル
SELECT name, tel FROM member UNION SELECT name, tel FROM staff ORDER BY name, tel ;
| name | tel |
|---|---|
| George | 3333333333333 |
| Henry | 66666666 |
| Jack | 22222222222 |
| Ken | |
| Mike | 1234567890 |
| Smith | 1111111111 |
| Tom |
-- 重複行をそのまま残す場合は、UNION ALL (Ken/Tomに注目) SELECT name, tel FROM member UNION ALL SELECT name, tel FROM staff ORDER BY name, tel ;
| name | tel |
|---|---|
| George | 3333333333333 |
| Henry | 66666666 |
| Jack | 22222222222 |
| Ken | |
| Ken | |
| Mike | 1234567890 |
| Smith | 1111111111 |
| Tom | |
| Tom |
【４】差（difference）
* 2つの集合で、 1つ目の集合にはあるけど、 2つ目の集合にはない
１）SQL
* 差演算は、EXCEPT / MINUS を使う * 重複行をそのまま残す場合は、EXCEPT ALL =>EXCEPT / MINUS については、以下の関連記事を参照のこと
差集合 ～ EXCEPT / MINUS etc ～
https://dk521123.hatenablog.com/entry/2021/05/26/142059
EXCEPT非対応DBの場合
* MySQLなど、EXCEPT / EXCEPT ALL がないDBの場合 => 外部結合 又は サブクエリを使う
２）サンプル
SELECT name, tel FROM member EXCEPT SELECT name, tel FROM staff ORDER BY name, tel ;
| name | tel |
|---|---|
| Mike | 1234567890 |
| Smith | 1111111111 |
EXCEPT非対応DBの場合
-- 外部結合 SELECT m.name, m.tel FROM member as m LEFT JOIN staff as s ON m.name = s.name AND m.tel = s.tel WHERE s.name IS NULL;
| name | tel |
|---|---|
| Mike | 1234567890 |
| Tom | |
| Smith | 1111111111 |
| Ken |
-- サブクエリ SELECT m.name, m.tel FROM member as m WHERE NOT EXISTS ( SELECT * FROM staff as s WHERE m.name = s.name AND m.tel = s.tel );
| name | tel |
|---|---|
| Mike | 1234567890 |
| Tom | |
| Smith | 1111111111 |
| Ken |
【５】共通（intersection）
* 2つ又は3つ以上の集合で、同じ要素を求める => A と B という集合があった場合、 その共通部分を算出する（積集合）
１）SQL
* INTERSECT を使う * 重複行をそのまま残す場合は、INTERSECT ALL
SQL ～ INTERSECT ～
https://dk521123.hatenablog.com/entry/2024/09/14/040940
INTERSECT 非対応DBの場合
* 内部結合、サブクエリ、IN句などを使用
２）サンプル
SELECT name, tel FROM member INTERSECT SELECT name, tel FROM staff ORDER BY name, tel ;
| name | tel |
|---|---|
| Ken | |
| Tom |
【６】直積（direct product）
* 2つ又は3つ以上の集合を掛け合わせる
１）SQL
* CROSS JOIN を使う => CROSS JOIN に関しては、以下の関連記事を参照のこと
テーブル結合 ～ 交差結合・CROSS JOIN ～
https://dk521123.hatenablog.com/entry/2011/12/21/000000
CROSS JOIN 非対応DBの場合
* FROM句に複数テーブルを指定し 結合条件を書かなければいい
２）サンプル
SELECT * FROM member CROSS JOIN staff; -- FROM句に複数テーブルを指定 SELECT * FROM member, staff;
| id | name | tel | updated_date | id | name | tel | updated_date |
|---|---|---|---|---|---|---|---|
| 1 | Mike | 1234567890 | 2024-09-14 | 1 | Tom | 2024-10-15 | |
| 1 | Mike | 1234567890 | 2024-09-14 | 2 | Ken | 2024-10-15 | |
| 1 | Mike | 1234567890 | 2024-09-14 | 3 | Henry | 66666666 | 2024-10-15 |
| 1 | Mike | 1234567890 | 2024-09-14 | 4 | George | 3333333333333 | 2024-10-14 |
| 1 | Mike | 1234567890 | 2024-09-14 | 5 | Jack | 22222222222 | 2024-10-14 |
| 2 | Tom | 2024-09-14 | 1 | Tom | 2024-10-15 | ||
| 2 | Tom | 2024-09-14 | 2 | Ken | 2024-10-15 | ||
| 2 | Tom | 2024-09-14 | 3 | Henry | 66666666 | 2024-10-15 | |
| 2 | Tom | 2024-09-14 | 4 | George | 3333333333333 | 2024-10-14 | |
| 2 | Tom | 2024-09-14 | 5 | Jack | 22222222222 | 2024-10-14 | |
| 3 | Smith | 1111111111 | 2024-09-14 | 1 | Tom | 2024-10-15 | |
| 3 | Smith | 1111111111 | 2024-09-14 | 2 | Ken | 2024-10-15 | |
| 3 | Smith | 1111111111 | 2024-09-14 | 3 | Henry | 66666666 | 2024-10-15 |
| 3 | Smith | 1111111111 | 2024-09-14 | 4 | George | 3333333333333 | 2024-10-14 |
| 3 | Smith | 1111111111 | 2024-09-14 | 5 | Jack | 22222222222 | 2024-10-14 |
| 4 | Ken | 2024-09-14 | 1 | Tom | 2024-10-15 | ||
| 4 | Ken | 2024-09-14 | 2 | Ken | 2024-10-15 | ||
| 4 | Ken | 2024-09-14 | 3 | Henry | 66666666 | 2024-10-15 | |
| 4 | Ken | 2024-09-14 | 4 | George | 3333333333333 | 2024-10-14 | |
| 4 | Ken | 2024-09-14 | 5 | Jack | 22222222222 | 2024-10-14 |
【７】選択（selection）
* ある条件を満足させる行を選び取る場合
１）SQL
* WHEREを使う（そんだけ）
２）サンプル
SELECT * FROM member WHERE name='Mike';
| id | name | tel | updated_date |
|---|---|---|---|
| 1 | Mike | 1234567890 | 2024-09-14 |
【８】射影（projection）
* 属性の部分集合から別の表を作ること
１）SQL
* SELECT句で列を指定する（そんだけ）
２）サンプル
SELECT name FROM member;
| name |
|---|
| Mike |
| Tom |
| Smith |
| Ken |
【９】結合（Join）
* 一方の表が他方の表を完全に含む要素だけを取り出す演算
１）SQL
* INNER JOINを使う（そんだけ）
２）サンプル
SELECT * FROM member as m INNER JOIN staff as s ON m.id = s.id ;
| id | name | tel | updated_date | id | name | tel | updated_date |
|---|---|---|---|---|---|---|---|
| 1 | Mike | 1234567890 | 2024-09-14 | 1 | Tom | 2024-10-15 | |
| 2 | Tom | 2024-09-14 | 2 | Ken | 2024-10-15 | ||
| 3 | Smith | 1111111111 | 2024-09-14 | 3 | Henry | 66666666 | 2024-10-15 |
| 4 | Ken | 2024-09-14 | 4 | George | 3333333333333 | 2024-10-14 |
【１０】商（division）
* 一方の表が他方の表を完全に含む要素だけを取り出す演算
１）SQL
* サブクエリを2つ組み合わせて実現
関連記事
SQLを書くコツ
https://dk521123.hatenablog.com/entry/2016/01/11/173055
相関サブクエリ / 自己相関サブクエリ
https://dk521123.hatenablog.com/entry/2016/01/23/230608
差集合 ～ EXCEPT / MINUS etc ～
https://dk521123.hatenablog.com/entry/2021/05/26/142059
SQL ～ INTERSECT ～
https://dk521123.hatenablog.com/entry/2024/09/14/040940
テーブルの結合 ～内部結合・INNER JOIN～
https://dk521123.hatenablog.com/entry/2010/02/05/131206
テーブルの結合 ～ 外部結合・LEFT/RIGHT/FULL JOIN ～
https://dk521123.hatenablog.com/entry/2012/05/06/212246
テーブル結合 ～ 交差結合・CROSS JOIN ～
https://dk521123.hatenablog.com/entry/2011/12/21/000000
テーブルの結合 ～完全結合・Union / Union All ～
https://dk521123.hatenablog.com/entry/2010/03/28/162247
全く関係ないテーブルを結合することを考える
https://dk521123.hatenablog.com/entry/2021/09/16/185552
Snowflake ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2024/09/12/003814
SQL ～ 集合演算の応用 ～
https://dk521123.hatenablog.com/entry/2024/09/16/223305
SQL ～ テーブル差分を調査することを考える ～
https://dk521123.hatenablog.com/entry/2024/09/22/220455
Window関数 ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2023/02/22/000000
