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

■ はじめに

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

目次

【0】データ準備
 1)使用データ
【1】方法1:差集合 EXCEPT / MINUS
 1)サンプル
 2)使用上の注意
【2】方法2:LEFT OUTER JOIN
 1)サンプル
 2)使用上の注意

【0】データ準備

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

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

1)使用データ

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') -- ★追加
;

【1】方法1:差集合 EXCEPT / MINUS

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

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

1)サンプル

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

2)使用上の注意

[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

【2】方法2:LEFT OUTER JOIN

新規データ差分抽出

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

変更データ差分抽出

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

1)サンプル

新規データ差分抽出

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

2)使用上の注意

[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] 削除は検知できない

--  「【1】方法1:差集合 EXCEPT / MINUS」の
-- 「2)使用上の注意」のサンプルを実行

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/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