2つのテーブルの差分をSQLで出す:追加・削除・変更された行

白い壁の横にある緑の木の扉 SQL

データ移行やバッチの修正のあとで、新旧2つのテーブルの中身が同じかを確かめたい場面があります。件数を数えるだけでは、行が入れ替わっていたり値が変わっていたりしても気づけません。片方にしかない行(追加・削除)と値が変わった行は、LEFT JOIN と NULL どうしも比べられる演算子 <=> を組み合わせると、1回のクエリでまとめて出せます。

<> で比べると、NULL から値に変わった行を見落とします。大文字と小文字だけが変わった行も、MySQL の初期設定のままでは差分に出てきません。この2つの落とし穴も、実行結果で確かめます。列が多いときに便利な UNION ALL と GROUP BY の書き方と、MySQL 8.0.31 から使える EXCEPT の書き方も紹介します。

以下の結果は MySQL 8.0 で実際に実行したものです。

このシナリオで考える

商品マスタを新しいテーブルへ移したとします。移行前が products_old、移行後が products_new です。列は同じで、category(分類)だけ NULL を入れられます。

CREATE TABLE products_old (
  id       INT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  price    INT NOT NULL,
  category VARCHAR(20)
);

CREATE TABLE products_new (
  id       INT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  price    INT NOT NULL,
  category VARCHAR(20)
);

INSERT INTO products_old VALUES
  (1, 'りんご',        120, '果物'),
  (2, 'みかん',         80, '果物'),
  (3, 'にんじん',       60, NULL),
  (4, 'キャベツ',      150, '野菜'),
  (5, '牛乳',          200, NULL),
  (7, 'USBケーブル',   980, '雑貨');

INSERT INTO products_new VALUES
  (1, 'りんご',        120, '果物'),
  (2, 'みかん',         90, '果物'),
  (3, 'にんじん',       60, '野菜'),
  (5, '牛乳',          200, NULL),
  (6, '卵',            250, '食品'),
  (7, 'usbケーブル',   980, '雑貨');

2つのテーブルには、わざと次の違いを入れてあります。id 1 と id 5 は同じ内容です(id 5 は分類がどちらも NULL)。

id 違い 見つけたい区分
2 価格が 80 から 90 に変わった 変更
3 分類が NULL から「野菜」に変わった 変更
4 移行後のテーブルにない 削除
6 移行後のテーブルにだけある 追加
7 名前の大文字・小文字が変わった 変更

まず件数を比べます。

SELECT
  (SELECT COUNT(*) FROM products_old) AS old_count,
  (SELECT COUNT(*) FROM products_new) AS new_count;
+-----------+-----------+
| old_count | new_count |
+-----------+-----------+
|         6 |         6 |
+-----------+-----------+

どちらも6件です。件数が合っていても、中身は5か所違います。

LEFT JOINで片方にしかない行を出す

追加と削除は、主キー(id)で LEFT JOIN して、相手側の id が NULL になった行を取れば出せます。相手のテーブルに同じ id がないと、相手側の列はすべて NULL で埋まるためです。

-- 移行後にしかない行(追加)
SELECT n.*
FROM products_new AS n
LEFT JOIN products_old AS o ON o.id = n.id
WHERE o.id IS NULL;
+----+------+-------+----------+
| id | name | price | category |
+----+------+-------+----------+
|  6 | 卵   |   250 | 食品     |
+----+------+-------+----------+
-- 移行前にしかない行(削除)
SELECT o.*
FROM products_old AS o
LEFT JOIN products_new AS n ON n.id = o.id
WHERE n.id IS NULL;
+----+--------------+-------+----------+
| id | name         | price | category |
+----+--------------+-------+----------+
|  4 | キャベツ     |   150 | 野菜     |
+----+--------------+-------+----------+

WHERE で見る列は、相手側の主キーにしてください。NULL を入れられる列(ここでは category)で判定すると、もとから NULL の行まで「片方にしかない行」に混ざります。

値が変わった行は<=>で比べる

両方にある行のうち、どこかの列が違う行を探します。素直に書くと、列ごとに <> で比べて OR でつなぐ形になります。

SELECT o.id, o.price AS old_price, n.price AS new_price,
       o.category AS old_category, n.category AS new_category
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE o.name <> n.name
   OR o.price <> n.price
   OR o.category <> n.category;
+----+-----------+-----------+--------------+--------------+
| id | old_price | new_price | old_category | new_category |
+----+-----------+-----------+--------------+--------------+
|  2 |        80 |        90 | 果物         | 果物         |
+----+-----------+-----------+--------------+--------------+

分類が NULL から「野菜」に変わった id 3 が出てきません。NULL との比較は、= でも <> でも結果が NULL(不明)になるからです。WHERE は結果が真の行だけを残すので、NULL になった行は捨てられます。

SELECT NULL = NULL, NULL <> '野菜', NULL <=> NULL, NULL <=> '野菜';
+-------------+------------------+---------------+-------------------+
| NULL = NULL | NULL <> '野菜'   | NULL <=> NULL | NULL <=> '野菜'   |
+-------------+------------------+---------------+-------------------+
|        NULL |             NULL |             1 |                 0 |
+-------------+------------------+---------------+-------------------+

MySQL の <=>(NULL 安全等価演算子)は、両方 NULL なら 1、片方だけ NULL なら 0 を返します。NULL が返ることはありません。この演算子で「全列が等しい」を作り、NOT で反転させると、変わった行だけが残ります。

SELECT o.id, o.price AS old_price, n.price AS new_price,
       o.category AS old_category, n.category AS new_category
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE NOT (o.name <=> n.name
       AND o.price <=> n.price
       AND o.category <=> n.category);
+----+-----------+-----------+--------------+--------------+
| id | old_price | new_price | old_category | new_category |
+----+-----------+-----------+--------------+--------------+
|  2 |        80 |        90 | 果物         | 果物         |
|  3 |        60 |        60 | NULL         | 野菜         |
+----+-----------+-----------+--------------+--------------+

id 3 が出るようになりました。分類が両方 NULL の id 5 は、同じ内容として扱われて出てきません。列が多いときは、行の形 (列1, 列2, ...) でまとめて比べることもできます。MySQL のリファレンスには、(a, b) <=> (x, y) は (a <=> x) AND (b <=> y) と同じだと書かれています。

SELECT o.id
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE NOT ((o.name, o.price, o.category) <=> (n.name, n.price, n.category));
+----+
| id |
+----+
|  2 |
|  3 |
+----+

<=> は MySQL 独自の書き方です。PostgreSQL では、標準 SQL の IS NOT DISTINCT FROM で同じ比較を書きます。NULL の比較のしくみは「SQLのNULLと三値論理:IS NULL・COALESCEの使い分け」で説明しています。

大文字と小文字の違いを見落とさない

ここまでの結果に、名前が「USBケーブル」から「usbケーブル」に変わった id 7 が出ていません。MySQL 8.0 の初期設定の照合順序(文字の比べ方の決まり)は utf8mb4_0900_ai_ci です。末尾の ci は大文字と小文字を区別しないという意味で、この2つの名前は「等しい」と判定されます。

SELECT 'USBケーブル' = 'usbケーブル' AS ci_equal,
       'USBケーブル' COLLATE utf8mb4_bin = 'usbケーブル' AS bin_equal;
+----------+-----------+
| ci_equal | bin_equal |
+----------+-----------+
|        1 |         0 |
+----------+-----------+

文字を1つずつ区別して比べたい列には、COLLATE utf8mb4_bin を付けます。

SELECT o.id, o.name AS old_name, n.name AS new_name
FROM products_old AS o
JOIN products_new AS n ON n.id = o.id
WHERE NOT (o.name COLLATE utf8mb4_bin <=> n.name
       AND o.price <=> n.price
       AND o.category <=> n.category);
+----+-----------------+-----------------+
| id | old_name        | new_name        |
+----+-----------------+-----------------+
|  2 | みかん          | みかん          |
|  3 | にんじん        | にんじん        |
|  7 | USBケーブル     | usbケーブル     |
+----+-----------------+-----------------+

移行で文字コードや照合順序を変えた場合は、名前やメールアドレスの列に付けておくと安心です。照合順序で結果が変わるほかの例は「MySQLの照合順序とutf8mb4:検索結果が変わる理由と設定」にまとめています。

追加・削除・変更を1回のクエリで出す

3つの結果を UNION ALL でつなぐと、1つの差分一覧になります。前半は移行前のテーブルから LEFT JOIN して「削除」と「変更」を出し、後半で「追加」を出します。MySQL には FULL OUTER JOIN(両側の外部結合)がないので、この組み立てで代わりにします。

SELECT o.id,
       CASE
         WHEN n.id IS NULL THEN '削除'
         ELSE '変更'
       END AS diff,
       o.name AS old_name, n.name AS new_name,
       o.price AS old_price, n.price AS new_price,
       o.category AS old_category, n.category AS new_category
FROM products_old AS o
LEFT JOIN products_new AS n ON n.id = o.id
WHERE n.id IS NULL
   OR NOT (o.name COLLATE utf8mb4_bin <=> n.name
       AND o.price <=> n.price
       AND o.category <=> n.category)
UNION ALL
SELECT n.id, '追加',
       NULL, n.name, NULL, n.price, NULL, n.category
FROM products_new AS n
LEFT JOIN products_old AS o ON o.id = n.id
WHERE o.id IS NULL
ORDER BY id;
+----+--------+-----------------+-----------------+-----------+-----------+--------------+--------------+
| id | diff   | old_name        | new_name        | old_price | new_price | old_category | new_category |
+----+--------+-----------------+-----------------+-----------+-----------+--------------+--------------+
|  2 | 変更   | みかん          | みかん          |        80 |        90 | 果物         | 果物         |
|  3 | 変更   | にんじん        | にんじん        |        60 |        60 | NULL         | 野菜         |
|  4 | 削除   | キャベツ        | NULL            |       150 |      NULL | 野菜         | NULL         |
|  6 | 追加   | NULL            | 卵              |      NULL |       250 | NULL         | 食品         |
|  7 | 変更   | USBケーブル     | usbケーブル     |       980 |       980 | 雑貨         | 雑貨         |
+----+--------+-----------------+-----------------+-----------+-----------+--------------+--------------+

最初に入れた5つの違いが、区分つきですべて出ました。前半と後半で同じ id が出ることはないので、重複を消す UNION にする必要はありません。変更前と変更後の値が横に並ぶため、どの列が変わったかもひと目でわかります。

列が多いときはUNION ALLとGROUP BYかEXCEPT

列が数十あると、<=> の条件を全部書くのは手間です。そのときは、2つのテーブルを縦に積んで全列で GROUP BY し、1件しかない組み合わせを残す書き方が使えます。

SELECT id, name, price, category, MAX(src) AS src
FROM (
  SELECT 'old' AS src, id, name, price, category FROM products_old
  UNION ALL
  SELECT 'new' AS src, id, name, price, category FROM products_new
) AS t
GROUP BY id, name, price, category
HAVING COUNT(*) = 1
ORDER BY id, src DESC;
+----+--------------+-------+----------+------+
| id | name         | price | category | src  |
+----+--------------+-------+----------+------+
|  2 | みかん       |    80 | 果物     | old  |
|  2 | みかん       |    90 | 果物     | new  |
|  3 | にんじん     |    60 | NULL     | old  |
|  3 | にんじん     |    60 | 野菜     | new  |
|  4 | キャベツ     |   150 | 野菜     | old  |
|  6 | 卵           |   250 | 食品     | new  |
+----+--------------+-------+----------+------+

GROUP BY は NULL どうしを同じグループにまとめるので、<=> を書かなくても id 5 は出ません。同じ id が old と new の2行で出たら変更、1行だけなら削除か追加です。各テーブルの中に同じ行が2件ない前提なので、主キーのあるテーブルで使ってください。

MySQL 8.0.31 以降なら、EXCEPT(差集合)でも書けます。上の結果から移行後にしかない行の組み合わせ、下の結果から移行前にしかない行の組み合わせがわかります。

SELECT id, name, price, category FROM products_new
EXCEPT
SELECT id, name, price, category FROM products_old
ORDER BY id;
+----+--------------+-------+----------+
| id | name         | price | category |
+----+--------------+-------+----------+
|  2 | みかん       |    90 | 果物     |
|  3 | にんじん     |    60 | 野菜     |
|  6 | 卵           |   250 | 食品     |
+----+--------------+-------+----------+
SELECT id, name, price, category FROM products_old
EXCEPT
SELECT id, name, price, category FROM products_new
ORDER BY id;
+----+--------------+-------+----------+
| id | name         | price | category |
+----+--------------+-------+----------+
|  2 | みかん       |    80 | 果物     |
|  3 | にんじん     |    60 | NULL     |
|  4 | キャベツ     |   150 | 野菜     |
+----+--------------+-------+----------+

EXCEPT は何も付けないと DISTINCT として動き、重複した行を1件にまとめます。3つの書き方の違いは次のとおりです。

書き方 NULL の扱い 出せるもの 使える版
LEFT JOIN と <=> <=> を書けば同じ値として扱う 追加・削除・変更の区分と、変更前後の値を横に並べた一覧 MySQL 5.x から
UNION ALL と GROUP BY 何もしなくても同じ値として扱う 違う行(old・new の印つき) MySQL 5.x から
EXCEPT 何もしなくても同じ値として扱う 片方にしかない行(変更された行を含む) MySQL 8.0.31 から

よくある落とし穴

GROUP BY と EXCEPT の結果には、id 7 の大文字・小文字の変更が出ていません。どちらも列の照合順序で同じかどうかを決めるので、utf8mb4_0900_ai_ci の列では「USBケーブル」と「usbケーブル」が同じ行とみなされます。大文字と小文字の違いも拾いたいときは、LEFT JOIN と <=> の書き方で、名前の列に COLLATE utf8mb4_bin を付けてください。

結合に使うキーにも気をつけます。移行のときに id を振り直していると、同じ商品でも id が合わないため、全行が「削除」と「追加」に出ます。その場合は、商品コードのような移行の前後で変わらない列で結合してください。

何百万行もあるテーブルでは、1回で全行を比べるとクエリが長く動き続けます。結合に使う列にインデックスがあることを確かめ、WHERE o.id BETWEEN 1 AND 100000 のように id の範囲で分けて比べると、1回あたりの負荷を抑えられます。

まとめ

2つのテーブルの差分は、主キーで LEFT JOIN して相手側の id が NULL の行を取れば追加と削除が、<=> で全列を比べれば変更がわかります。<> で比べると NULL から値に変わった行を落とすので、NULL を含む列では必ず <=> を使います。大文字と小文字の違いまで見るなら COLLATE utf8mb4_bin を付けてください。列が多くて条件を書くのが手間なときは、UNION ALL と GROUP BY、または MySQL 8.0.31 以降の EXCEPT が使えます。

もっと学ぶなら【PR】

※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。

参考リンク

アイキャッチ画像: Photo by Alessandro Venturi on Unsplash

タイトルとURLをコピーしました