MySQL で2つのテーブル間で不一致のレコードを抽出する

IT

はじめに

本記事では2つのテーブル間で不一致のレコードを抽出する方法についてまとめます。
検証には MySQL が公式で用意してくれているデータを使用します。

環境

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.01 sec)

サンプルSQL

以下SQLでemployeeテーブルを比較することができます。
検証のため、employeeテーブルを編集 & employeeテーブルと中身が若干異なるemployee2テーブルを作成します。

CREATE TABLE employees2(
    SELECT *
    FROM employees
);

DELETE
FROM
    employees
WHERE
    emp_no = '10004';

SELECT *
FROM employees;
-- # emp_no, birth_date, first_name, last_name, gender, hire_date
-- '10001', '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26'
-- '10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21'
-- '10003', '1959-12-03', 'Parto', 'Bamford', 'M', '1986-08-28'
-- '10005', '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12'
-- '10006', '1953-04-20', 'Anneke', 'Preusig', 'F', '1989-06-02'
-- ...

UPDATE
    employees2
SET
    emp_no = '10002',
    birth_date = '1964-06-02',
    first_name = 'Bezalel',
    last_name = 'Simmel',
    gender = 'F',
    hire_date = '1985-11-21'
WHERE
    emp_no = '10003';

SELECT *
FROM employees2;
-- # emp_no, birth_date, first_name, last_name, gender, hire_date
-- '10001', '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26'
-- '10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21'
-- '10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21'
-- '10004', '1954-05-01', 'Chirstian', 'Koblick', 'M', '1986-12-01'
-- '10005', '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12'
-- '10006', '1953-04-20', 'Anneke', 'Preusig', 'F', '1989-06-02'
-- ...

以下SQLでテーブルを比較します。
以下ポイントです。

  • 比較対象のカラム(今回は全カラム)でGROUP BYし、COUNTを取ることで、重複レコードがあった場合にも検出できる
  • NULLを含む列が存在する場合もCOALESCEを使用することで比較できる
SELECT *
FROM (SELECT emp_no, birth_date, first_name, last_name, gender, hire_date, COUNT(*) AS count
      FROM employees
      GROUP BY emp_no, birth_date, first_name, last_name, gender, hire_date) t1
WHERE NOT EXISTS(
        SELECT 1
        FROM (SELECT emp_no, birth_date, first_name, last_name, gender, hire_date, COUNT(*) AS count
              FROM employees2
              GROUP BY emp_no, birth_date, first_name, last_name, gender, hire_date) t2
        WHERE t1.emp_no = t2.emp_no
          AND t1.birth_date = t2.birth_date
          AND t1.first_name = t2.first_name
          AND t1.last_name = t2.last_name
          AND t1.gender = t2.gender
          AND t1.hire_date = t2.hire_date
          AND t1.count = t2.count
    )
UNION ALL
SELECT *
FROM (SELECT emp_no, birth_date, first_name, last_name, gender, hire_date, COUNT(*) AS count
      FROM employees2
      GROUP BY emp_no, birth_date, first_name, last_name, gender, hire_date) t1
WHERE NOT EXISTS(
        SELECT 1
        FROM (SELECT emp_no, birth_date, first_name, last_name, gender, hire_date, COUNT(*) AS count
              FROM employees
              GROUP BY emp_no, birth_date, first_name, last_name, gender, hire_date) t2
        WHERE t1.emp_no = t2.emp_no
          AND t1.birth_date = t2.birth_date
          AND t1.first_name = t2.first_name
          AND t1.last_name = t2.last_name
          AND t1.gender = t2.gender
          AND t1.hire_date = t2.hire_date
          AND t1.count = t2.count
    );
-- # emp_no, birth_date, first_name, last_name, gender, hire_date, count
-- '10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21', '1'
-- '10003', '1959-12-03', 'Parto', 'Bamford', 'M', '1986-08-28', '1'
-- '10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21', '2'
-- '10004', '1954-05-01', 'Chirstian', 'Koblick', 'M', '1986-12-01', '1'

おわりに

本記事では2つのテーブル間で不一致のレコードを抽出する方法についてまとめました。
複数環境のデータ比較などの際に役立てていただければと思います。
この記事がどなたかの参考になれば幸いです。

参考

コメント