【SQL】テーブルの結合 ～完全結合・Union / Union All ～

SQL

■ はじめに

https://dk521123.hatenablog.com/entry/2023/01/16/000000

で、SQLは、集合演算だと説明した。

今回は、集合演算の足し算を表す
SQLの UNION / UNION ALL について、学ぶ。

目次

【１】 UNION
　１）構文
　２）サンプル
【２】 UNION ALL
　１）構文
　２）サンプル
　３） 補足：UNION ALL の 使い方
【３】 Union と Union All の違い
　１）サンプル
　２）まとめ
【４】 使用上の注意

【１】 UNION

* レコードを足し合わせる機能
* 1つの条件 + 別の条件を繋げる時（和集合の実現）に役立つ

※ 和集合とは... 

* 2つの集合A, Bに対して、どちらの要素にも入る

SQL ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2023/01/16/000000

１）構文

SELECT [item name1]
FROM [table name1]
UNION
SELECT [item name2]
FROM [table name2]

 ２）サンプル

SELECT u.user_name AS name
FROM users AS u
UNION
SELECT e.emp_name AS name
FROM employee AS e;

【２】 UNION ALL

* Union で重複含む版

 １）構文

SELECT [item name1]
FROM [table name1]
UNION ALL
SELECT [item name2]
FROM [table name2]

 ２）サンプル

例１：簡単なデータ 

SELECT u.user_name AS name
FROM users AS u
UNION ALL
SELECT e.emp_name AS name
FROM employee AS e;

例２：重複ユーザを排除した結果を表示 

-- １－１）昨日のデータ格納用テーブル
CREATE TABLE "user_20200615" (
    "id" INT NOT NULL,
    "name" TEXT NULL DEFAULT NULL,
    PRIMARY KEY ("id")
);
-- １－２）今日のデータ格納用テーブル（１－１）と同等のテーブル）
CREATE TABLE "user_20200616" (
    "id" INT NOT NULL,
    "name" TEXT NULL DEFAULT NULL,
    PRIMARY KEY ("id")
);
-- ２）サンプルデータ
INSERT INTO user_20200615 VALUES(1, 'Mike'),(2, 'Smith');
INSERT INTO user_20200616 VALUES(2, 'Smith'),(3, 'Tom');

-- ３）重複ユーザを排除した結果を表示（★今回のテーマ★）

-- 今日データ
SELECT
  main.id, main.name
FROM 
  user_20200616 AS main
UNION ALL
-- 昨日データ
-- => 昨日データだけ欲しいので、LEFT OUTER JOINで結合した結果に対して
--  　「WHERE new.id IS NULL」今日データが含まれているデータを省く
SELECT
  old.id, old.name
FROM 
  user_20200615 AS old
LEFT OUTER JOIN
  user_20200616 AS new
ON
  old.id = new.id
WHERE
  new.id IS NULL
ORDER BY id

-- 出力結果は以下の通り（重複データ「2 Smith」がまとまって表示）
id name
1 Mike
2 Smith
3 Tom

３） 補足：UNION ALL の 使い方

* リポートとしても使える

サンプル 

SELECT
 1 AS no,
 'data count' AS item,
 (SELECT COUNT(u.user_id) FROM users AS u) AS value
UNION ALL
SELECT
 2 AS no,
 'max id' AS item,
 (SELECT MAX(u.user_id) FROM users AS u) AS value
UNION ALL
SELECT
 3 AS no,
 'min id' AS item,
 (SELECT MIN(u.user_id) FROM users AS u) AS value

出力結果例

no item value
1 data count 6
2 max id 6
3 min id 1

 【３】 Union と Union All の違い

 Union 

 * DISTINCT(重複を統合)のような形で結果を返す
* 集合だと 「A + B - (A^B; AとBの共通部分)」

 Union All 

 * 重複も含めて、全ての結果を返す
* 集合だと A + B

１）サンプル

データ準備 

CREATE TABLE member_old (
  id       INT,
  name     VARCHAR(50),
  gender     VARCHAR(1),
  country_code     VARCHAR(3),
  class    VARCHAR(10),
  updated_date    DATE
);

CREATE TABLE member_new (
  id       INT,
  name     VARCHAR(50),
  gender     VARCHAR(1),
  country_code     VARCHAR(3),
  class    VARCHAR(10),
  updated_date    DATE
);

-- INSERT member_old
INSERT INTO member_old (id,name,gender,country_code,class,updated_date) VALUES
  (1,'Mike','m','USA','bronze','2024-09-14'),
  (2,'Tom',NULL,'GBR','gold','2024-09-14'),
  (3,'Smith','f',NULL,'silver','2024-09-14'),
  (4,'Ken',NULL,'JPN','gold','2024-09-14'),
  (5,'Jack','m','UKR','gold','2024-09-14')
;

-- INSERT member_new
INSERT INTO member_new
 (id,name,gender,country_code,class,updated_date) VALUES
  (1,'Mike','m','USA','bronze','2024-09-14'),
  (2,'Tom','m','GBR','platinum','2024-09-15'), -- ★変更
  (3,'Smith','f',NULL,'silver','2024-09-14'),
  (4,'Ken','m','JPN','gold','2024-09-15'), -- ★変更
  (5,'Jack','m','UKR','gold','2024-09-14'),
  (6,'Henry','m','AUS','silver','2024-09-15') -- ★追加
;

UNION の場合 

SELECT * FROM member_old
UNION
SELECT * FROM member_new
ORDER BY id;

-- => 重複削除されている（例えば、id = 1 が1つだけ表示）
id name gender country_code class updated_date
1 Mike m USA bronze 2024-09-14
2 Tom m GBR platinum 2024-09-15
2 Tom GBR gold 2024-09-14
3 Smith f silver 2024-09-14
4 Ken m JPN gold 2024-09-15
4 Ken JPN gold 2024-09-14
5 Jack m UKR gold 2024-09-14
6 Henry m AUS silver 2024-09-15

UNION ALL の場合 

SELECT * FROM member_old
UNION ALL
SELECT * FROM member_new
ORDER BY id;

-- => 重複表示 を表示している（例えば、id = 1 が2重にある）
id name gender country_code class updated_date
1 Mike m USA bronze 2024-09-14
1 Mike m USA bronze 2024-09-14
2 Tom m GBR platinum 2024-09-15
2 Tom GBR gold 2024-09-14
3 Smith f silver 2024-09-14
3 Smith f silver 2024-09-14
4 Ken JPN gold 2024-09-14
4 Ken m JPN gold 2024-09-15
5 Jack m UKR gold 2024-09-14
5 Jack m UKR gold 2024-09-14
6 Henry m AUS silver 2024-09-15

２）まとめ

UNION UNION ALL
重複行 重複削除 重複表示

 【４】 使用上の注意

 * ORDER BYを利用する時には、
   フィールドを取得しておかなければならない
 （★これに、はまった★）

 * 取得するフィールドの数/型が、一致しなければならない
 （SELECT句のカラム数が同じ）

 * 以上のことが考慮されていれば、
   異なるテーブルであっても問題なし。

 参考資料

http://www.atmarkit.co.jp/fnetwork/rensai/sql09/sql1.html
http://www.techscore.com/tech/sql/08_01.html/
サンプルが分かりやすい
http://www.dbonline.jp/mysql/select/index18.html

関連記事

テーブルの結合 ～内部結合・INNER JOIN～
https://dk521123.hatenablog.com/entry/2010/02/05/131206
テーブルの結合 ～外部結合・LEFT OUTER JOIN、RIGHT JOIN～
https://dk521123.hatenablog.com/entry/2012/05/06/212246
テーブルの結合 ～交差結合・CROSS JOIN～
https://dk521123.hatenablog.com/entry/2011/12/21/000000
全く関係ないテーブルを結合することを考える
https://dk521123.hatenablog.com/entry/2021/09/16/185552
SQL ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2023/01/16/000000