# Report the differences between two tables

Join / diff

A variant of [Compare two tables](https://meridian.online/operations/compare-two-tables).

List where two tables differ, cell by cell, column by column or as summary statistics.

Page: <https://meridian.online/operations/compare-two-tables/report-the-differences-between-two-tables>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

report differences cell by cell, column by column and as summary figures

```sql
CREATE TEMP TABLE v1 AS SELECT * FROM (VALUES
  (1, 'ann', 10, 1.5), (2, 'bob', 5, NULL), (3, 'cy', 7, 2.0), (4, 'di', 1, 3.0), (6, 'fay', 3, 1.0), (NULL, 'nk', 1, 1.0)) t(id, name, qty, price);
CREATE TEMP TABLE v2 AS SELECT * FROM (VALUES
  (1, 'ann', 12, 1.5), (2, 'bob', 5, 2.5), (3, 'cyd', 8, 2.0), (5, 'ed', 2, 4.0), (6, 'fay', 3, 1.0), (NULL, 'nk', 1, 1.0)) t(id, name, qty, price);
-- the key must be unique on each side, or a record is compared more than once; a NULL key matches nothing, not even itself
CREATE TEMP TABLE repeated_keys AS
SELECT 'v1' AS side, id FROM v1 WHERE id IS NOT NULL GROUP BY id HAVING count(*) > 1
UNION ALL SELECT 'v2', id FROM v2 WHERE id IS NOT NULL GROUP BY id HAVING count(*) > 1;
-- every column as text, one row per key and column
CREATE TEMP TABLE old_cells AS
FROM (SELECT id, name::VARCHAR AS name, qty::VARCHAR AS qty, price::VARCHAR AS price FROM v1)
UNPIVOT INCLUDE NULLS (val FOR col IN (name, qty, price));
CREATE TEMP TABLE new_cells AS
FROM (SELECT id, name::VARCHAR AS name, qty::VARCHAR AS qty, price::VARCHAR AS price FROM v2)
UNPIVOT INCLUDE NULLS (val FOR col IN (name, qty, price));
-- cell by cell: one row for each cell that differs; IS DISTINCT FROM also sees NULL turning into a value
CREATE TEMP TABLE cell_report AS
SELECT id, col, o.val AS old_value, n.val AS new_value
FROM old_cells o JOIN new_cells n USING (id, col) WHERE o.val IS DISTINCT FROM n.val;
-- column by column: one row for each column compared
CREATE TEMP TABLE column_report AS
SELECT col, count(*) AS compared, count(*) FILTER (WHERE o.val IS DISTINCT FROM n.val) AS differing
FROM old_cells o JOIN new_cells n USING (id, col) GROUP BY col;
-- summary figures: one row for the two tables
CREATE TEMP TABLE summary AS
SELECT (SELECT count(*) FROM v1) AS rows_v1, (SELECT count(*) FROM v2) AS rows_v2,
       count(*) AS matched, count(*) FILTER (WHERE a IS DISTINCT FROM b) AS changed, count(*) FILTER (WHERE a IS NOT DISTINCT FROM b) AS identical,
       (SELECT count(*) FROM v1 ANTI JOIN v2 ON v1.id = v2.id) AS only_in_v1, (SELECT count(*) FROM v2 ANTI JOIN v1 ON v1.id = v2.id) AS only_in_v2
FROM v1 a JOIN v2 b ON a.id = b.id;
SELECT * FROM cell_report ORDER BY id, col;
SELECT * FROM column_report ORDER BY col;
SELECT * FROM summary;
SELECT CASE WHEN ((SELECT count(*) = 0 FROM repeated_keys)
  AND (SELECT list((id, col, old_value, new_value) ORDER BY id, col) IS NOT DISTINCT FROM [(1, 'qty', '10', '12'), (2, 'price', NULL, '2.5'), (3, 'name', 'cy', 'cyd'), (3, 'qty', '7', '8')] FROM cell_report)
  AND (SELECT map_from_entries(list((col, differing) ORDER BY col)) = MAP {'name': 1, 'price': 1, 'qty': 2} AND min(compared) = 4 AND max(compared) = 4 FROM column_report)
  AND (SELECT rows_v1 = 6 AND rows_v2 = 6 AND matched = 4 AND changed = 3 AND identical = 1 AND only_in_v1 = 2 AND only_in_v2 = 2 FROM summary)) THEN 'as expected' ELSE error('the report should hold 4 cells (qty of 1 and 3, the price of 2 turning from NULL to 2.5, the name of 3), 4 compared records in each column, and 4 matched records of which 3 changed, with ids 4 and 5 and the two NULL-key rows each unmatched') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Bruin: [`data-diff`](https://getbruin.com/docs/bruin/commands/data-diff.html) (read 2026-09-27)
- Duckle: [`Reconcile (Source vs Target)`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- EasyMorph: [`Compare tables`](https://help.easymorph.com/doku.php?id=transformations:compare) (read 2026-09-27)
- Apache Hop: [`Table Compare`](https://hop.apache.org/manual/latest/pipeline/transforms/tablecompare.html) (read 2026-09-27)
- KNIME Analytics Platform: [`Table Difference Finder`](https://api.hub.knime.com/nodes/*36DYRFQTvb7J5vmF?details=full) (read 2026-09-27)
- Pentaho Data Integration: [`Table Compare`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- VisiData: [`setdiff-sheet`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/experimental/diff_sheet.py#L29) (read 2026-09-23)