Join / diff · All operations

Check that two tables are identical

A variant of Compare two tables

Return whether two tables hold the same data.

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

AWS Glue (1)
  • DatasetMatchread 2026-09-27

    Compare a dataset with a reference dataset and tell whether they are in sync

Polars (1)

Background

In Polars, DataFrame.equals checks whether a DataFrame is equal to another DataFrame and returns a Boolean.[1] In pandas, DataFrame.equals returns True if all elements are the same in both objects, and False otherwise.[2]

When to use it

In Polars, df1.equals(df2) returns False when df2 holds the same rows as df1 in reverse order.[1] Sort both tables on the same columns first when row order is not part of what should match. (Meridian's view) In pandas, DataFrame.equals requires corresponding columns and index to be of the same dtype.[2]

In Polars, DataFrame.equals takes a null_equal parameter, True by default, that considers null values as equal.[1] In pandas, DataFrame.equals considers NaNs in the same location equal.[2]

In AWS Glue, a DatasetMatch rule checks whether the data in a primary dataset matches a reference dataset, joining the two on key columns.[3] In AWS Glue, a DatasetMatch rule needs join keys that are unique and not NULL.[3] When a verdict says the tables differ, follow it with a difference report to find where. (Meridian's view)

The method

The SQL EXCEPT ALL operator does not remove duplicates: a row that appears X times in the first query and Y times in the second appears X minus Y times, or not at all.[4] In DuckDB, vanilla EXCEPT follows set semantics and eliminates duplicates, while EXCEPT ALL uses bag semantics.[5] For row elimination and duplicate removal, the SQL EXCEPT operator does not distinguish between NULLs.[4] Treat two tables as holding the same rows only when EXCEPT ALL in each direction returns nothing, so that a missing repeat is caught. (Meridian's view)

Compared with the alternatives

In pandas, DataFrame.eq returns a DataFrame where each element is True if the respective elements are equal, where DataFrame.equals returns one Boolean.[2] A single verdict suits a test or a pipeline gate; a per-cell or per-row comparison suits the work of finding what changed. (Meridian's view)

Sources

  1. [1]polars.DataFrame.equals — Polars documentationdocumentation
  2. [2]pandas.DataFrame.equals — pandas 3.1.1 documentationdocumentation
  3. [3]DatasetMatch - AWS Gluedocumentation
  4. [4]Set operations (SQL) - Wikipediasecondary source
  5. [5]Set Operations – DuckDBdocumentation