# Check that two tables are identical

Join / diff

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

Return whether two tables hold the same data.

Page: <https://meridian.online/operations/compare-two-tables/check-that-two-tables-are-identical>

## Status

Runs as a SQL step.

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

## The DuckDB step

check whether two tables are identical

```sql
CREATE TEMP TABLE x AS SELECT * FROM (VALUES (1, 'a'), (1, 'a'), (2, NULL), (3, 'c')) t(id, v);
-- the same rows in another order
CREATE TEMP TABLE same AS SELECT * FROM (VALUES (3, 'c'), (1, 'a'), (2, NULL), (1, 'a')) t(id, v);
-- one copy of (1, a) fewer
CREATE TEMP TABLE fewer AS SELECT * FROM (VALUES (1, 'a'), (2, NULL), (3, 'c')) t(id, v);
-- NULL turned into a value
CREATE TEMP TABLE changed AS SELECT * FROM (VALUES (1, 'a'), (1, 'a'), (2, 'b'), (3, 'c')) t(id, v);
-- identical when neither table has a row the other lacks, counting repeats (EXCEPT ALL) and treating NULLs as equal
CREATE TEMP TABLE verdict AS
SELECT (SELECT count(*) FROM (SELECT * FROM x EXCEPT ALL SELECT * FROM same)) + (SELECT count(*) FROM (SELECT * FROM same EXCEPT ALL SELECT * FROM x)) = 0 AS x_same,
       (SELECT count(*) FROM (SELECT * FROM x EXCEPT ALL SELECT * FROM fewer)) + (SELECT count(*) FROM (SELECT * FROM fewer EXCEPT ALL SELECT * FROM x)) = 0 AS x_fewer,
       (SELECT count(*) FROM (SELECT * FROM x EXCEPT ALL SELECT * FROM changed)) + (SELECT count(*) FROM (SELECT * FROM changed EXCEPT ALL SELECT * FROM x)) = 0 AS x_changed,
       -- EXCEPT without ALL ignores repeats, so it would call x and fewer identical
       (SELECT count(*) FROM (SELECT * FROM x EXCEPT SELECT * FROM fewer)) + (SELECT count(*) FROM (SELECT * FROM fewer EXCEPT SELECT * FROM x)) = 0 AS set_x_fewer;
SELECT * FROM verdict;
SELECT CASE WHEN ((SELECT x_same AND NOT x_fewer AND NOT x_changed AND set_x_fewer FROM verdict)) THEN 'as expected' ELSE error('the table should be identical to its reordered copy and different from the copy with one repeat fewer and from the copy with a NULL changed to a value, and EXCEPT without ALL should miss the missing repeat') 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).

- AWS Glue: [`DatasetMatch`](https://docs.aws.amazon.com/glue/latest/dg/dqdl-rule-types.html) (read 2026-09-27)
- Polars: [`DataFrame.equals`](https://docs.pola.rs/api/python/stable/reference/dataframe/miscellaneous.html) (read 2026-09-27)