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