Get Row-Level Differences Between Two MySQL Tables

Get row-level differences between two MySQL tables — EXCEPT (MySQL 8.0.31+), LEFT JOIN, and UNION tricks with copy-paste queries and when to use each.

Row level diffrences in MySQL

Last updated: July 2026

Disclosure: This post contains affiliate links. If you make a purchase through these links, I may earn a commission at no extra cost to you. Read the full Affiliate Disclosure.

You have orders and orders_backup and need to know exactly which rows differ. Here are four queries, from the modern one-liner to techniques that work on any MySQL version.

1. EXCEPT — the modern way (MySQL 8.0.31+)

MySQL finally supports EXCEPT and INTERSECT:

-- rows in table_a that are NOT in table_b
SELECT * FROM table_a
EXCEPT
SELECT * FROM table_b;

-- run it the other way around to find rows only in table_b
SELECT * FROM table_b
EXCEPT
SELECT * FROM table_a;

Cleanest syntax, compares every column, and NULLs are handled correctly. Requires MySQL 8.0.31 or newer (SELECT VERSION(); to check).

2. LEFT JOIN … IS NULL — works everywhere

SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;

Finds rows whose key exists only in table_a. To also catch rows where the key matches but other columns changed:

SELECT a.*
FROM table_a a
LEFT JOIN table_b b
  ON  a.id     = b.id
  AND a.status <=> b.status
  AND a.total  <=> b.total
WHERE b.id IS NULL;

The NULL-safe operator <=> treats NULL = NULL as true — with a plain =, any NULL column silently marks the row as different.

3. UNION ALL + HAVING — symmetric diff in one query

SELECT id, status, total, MIN(src) AS only_in
FROM (
  SELECT 'table_a' AS src, id, status, total FROM table_a
  UNION ALL
  SELECT 'table_b' AS src, id, status, total FROM table_b
) t
GROUP BY id, status, total
HAVING COUNT(*) = 1
ORDER BY id;

Every row appearing in only one table shows up once, with only_in telling you which side. My default on pre-8.0.31 servers — one pass, both directions.

4. Quick check first: CHECKSUM TABLE

CHECKSUM TABLE table_a, table_b;

Equal checksums = identical data; skip the row-level queries entirely. Different checksums = run query 1 or 3 to see the actual rows.

Performance note

For large tables, make sure the join/grouping columns are indexed, and restrict the column list to what actually matters — SELECT * diffs on wide tables with TEXT columns get slow. If you need a full schema comparison rather than data, see comparing two MySQL databases in Workbench.

Comments

comments