【SQL】SQL ～ テーブル差分を調査することを考える ～

SQL

■ はじめに

システムのマイグレーションで
テーブル差分を調査する必要ができたので
そのやり方を調べてみた

目次

【０】データ準備
　１）使用データ
【１】方法１：差集合 EXCEPT / MINUS
　１）サンプル
　２）使用上の注意
【２】方法２：LEFT OUTER JOIN
　１）サンプル
　２）使用上の注意

【０】データ準備

* 試すDBがなければ、以下のサイトで行うといいかも、、、
* PostgreSQL15を想定

SQL Fiddle
https://www.db-fiddle.com/

１）使用データ

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

CREATE TABLE IF NOT EXISTS 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'),
  (6,'Henry','m','AUS','bronze','2024-09-14'),
  (7,'Sophia','f','JPN','platinum','2024-09-14'),
  (8,'George','m','USA',NULL,'2024-09-14'),
  (9,'Lily','f','JPN','bronze','2024-09-14'),
  (10,'Daisy','f','USA','platinum','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'), -- ★変更
  (7,'Sophia','f','JPN','platinum','2024-09-14'),
  (8,'George','m','USA','silver','2024-09-15'), -- ★変更
  (9,'Lily','f','JPN','bronze','2024-09-14'),
  (10,'Daisy','f','USA','platinum','2024-09-14'),
  (11,'Leo','m',NULL,NULL,'2024-09-15'), -- ★追加
  (12,'Sam',NULL,'AUS','bronze','2024-09-15') -- ★追加
;

【１】方法１：差集合 EXCEPT / MINUS

* 差集合で差分を抽出する
 => 差集合の詳細は、以下の関連記事を参照のこと

差集合 ～ EXCEPT / MINUS etc ～
https://dk521123.hatenablog.com/entry/2021/05/26/142059

１）サンプル

SELECT
 id,
 name,
 gender,
 country_code,
 class,
 updated_date
FROM
 member_new
-- ★注目★
EXCEPT
SELECT
 id,
 name,
 gender,
 country_code,
 class,
 updated_date
FROM
 member_old
ORDER BY
 id
;
id name gender country_code class updated_date
2 Tom m GBR platinum 2024-09-15T00:00:00.000Z
4 Ken m JPN gold 2024-09-15T00:00:00.000Z
6 Henry m AUS silver 2024-09-15T00:00:00.000Z
8 George m USA silver 2024-09-15T00:00:00.000Z
11 Leo m 2024-09-15T00:00:00.000Z
12 Sam AUS bronze 2024-09-15T00:00:00.000Z

２）使用上の注意

[1] あくまで MINUS（差）なので、TABLE_A MINUS TABLE_B だとしたら
　TABLE_B の方にTABLE_A にない項目があった場合、検知できない

例 

-- INSERT member_old
TRUNCATE TABLE member_old;
INSERT INTO member_old (id,name,gender,country_code,class,updated_date) VALUES
  (1,'Mike','m','USA',NULL,'2024-09-14'),
  --(2,'Tom',NULL,'GBR',NULL,'2024-09-14'), -- ★コメントアウト★
  (3,'Smith','f',NULL,NULL,'2024-09-14'),
  (4,'Ken',NULL,'JPN',NULL,'2024-09-14')
;

-- INSERT member_new
TRUNCATE TABLE member_new;
INSERT INTO member_new
 (id,name,gender,country_code,class,updated_date) VALUES
  (1,'Mike','m','USA',NULL,'2024-09-14'),
  (2,'Tom',NULL,'GBR',NULL,'2024-09-14'),
  --(3,'Smith','f',NULL,NULL,'2024-09-14'), -- ★コメントアウト★
  (4,'Ken',NULL,'JPN',NULL,'2024-09-14')
;

出力結果 

* id = 2 が追加されたことを検知できるが
　id=3を検知できる訳じゃない
id name gender country_code class updated_date
2 Tom GBR 2024-09-14

id = 3 を検知するには 

SELECT
 id,
 name,
 gender,
 country_code,
 class,
 updated_date
FROM
-- member_new -- ★コメントアウト
 member_old -- ★逆にした
EXCEPT
SELECT
 id,
 name,
 gender,
 country_code,
 class,
 updated_date
FROM
 -- member_old -- ★コメントアウト
 member_new -- ★逆にした
;
id name gender country_code class updated_date
3 Smith f 2024-09-14

【２】方法２：LEFT OUTER JOIN

新規データ差分抽出 

* LEFT OUTER JOIN すると
　整合性を保つために該当なし部分に関しては
　nullで処理されるという性質があるので
　それを利用して「WHERE ＜項目＞ IS NULL」すると
　差分が取得できる

変更データ差分抽出 

* 各データが異なるかをWHERE句で追加する

１）サンプル

新規データ差分抽出 

SELECT
  *
FROM
  member_old AS old
LEFT OUTER JOIN
  member_new AS new
ON
  old.id = new.id
-- ★ここに注目★ 
WHERE old.id IS NULL
;
id name gender country_code class updated_date id name gender country_code class updated_date
11 Leo m 2024-09-15
12 Sam AUS bronze 2024-09-15

変更データ差分抽出 

SELECT
  *
FROM
  member_new AS new
LEFT OUTER JOIN
  member_old AS old
ON
  old.id = new.id
WHERE
  old.name <> new.name
  OR old.gender <> new.gender
  OR old.country_code <> new.country_code
  OR old.class <> new.class
  OR old.updated_date <> new.updated_date
;
id name gender country_code class updated_date id name gender country_code class updated_date
2 Tom m GBR platinum 2024-09-15 2 Tom GBR gold 2024-09-14
4 Ken m JPN gold 2024-09-15 4 Ken JPN gold 2024-09-14
6 Henry m AUS silver 2024-09-15 6 Henry m AUS bronze 2024-09-14
8 George m USA silver 2024-09-15 8 George m USA 2024-09-14

２）使用上の注意

[1] どの差分をみたいかによって、差分抽出できなくなる 

-- サンプル
SELECT
  *
FROM
  member_old AS old -- 逆にした
LEFT OUTER JOIN
  member_new AS new -- 逆にした
ON
  old.id = new.id
WHERE new.id IS NULL
;
-- データが出ない（当たり前なのだが）

[2] 削除は検知できない 

--  「【１】方法１：差集合 EXCEPT / MINUS」の
-- 「２）使用上の注意」のサンプルを実行

SELECT
  *
FROM
  member_old AS new
LEFT OUTER JOIN
  member_old AS old
ON
  old.id = new.id
WHERE
  old.name <> new.name
  OR old.gender <> new.gender
  OR old.country_code <> new.country_code
  OR old.class <> new.class
  OR old.updated_date <> new.updated_date
;
-- 何も検知されない

参考文献

https://style.potepan.com/articles/26163.html

* 以下は、今回紹介した方法以外にも紹介されている

https://note.com/shuma__rrrrr/n/nd637b713106b

関連記事

SQL ～ 大文字 / 小文字が含まれているか判定 ～
https://dk521123.hatenablog.com/entry/2024/11/07/141021
SQL ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2023/01/16/000000
SQL ～ 集合演算の応用 ～
https://dk521123.hatenablog.com/entry/2024/09/16/223305
Snowflake ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2024/09/12/003814
テーブルの結合 ～完全結合・Union / Union All ～
https://dk521123.hatenablog.com/entry/2010/03/28/162247
差集合 ～ EXCEPT / MINUS etc ～
https://dk521123.hatenablog.com/entry/2021/05/26/142059
SQL ～ INTERSECT ～
https://dk521123.hatenablog.com/entry/2024/09/14/040940
Window関数 ～ LAG / LEAD ～
https://dk521123.hatenablog.com/entry/2021/09/10/092850
WITH句 ～ 共通テーブル式 ～
https://dk521123.hatenablog.com/entry/2012/07/26/013620

ランキング参加中
プログラミング