Join / union · All operations

Append tables and drop duplicate rows

A variant of Append tables

Stack the rows of two or more tables and keep only one copy of each distinct row.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Append three tables and keep one copy of each distinct row

SQL
CREATE TEMP TABLE t1 AS SELECT * FROM (VALUES (1, 'a'), (1, 'a'), (2, NULL)) t(id, v);
CREATE TEMP TABLE t2 AS SELECT * FROM (VALUES (2, NULL), (3, 'c')) t(id, v);
CREATE TEMP TABLE t3 AS SELECT * FROM (VALUES ('c', 3), (NULL, 4)) t(v, id);
-- UNION keeps distinct rows (NULLs count as equal); BY NAME lines t3 up by column name although its columns are in the other order
CREATE TEMP TABLE result AS
SELECT * FROM t1 UNION BY NAME SELECT * FROM t2 UNION BY NAME SELECT * FROM t3;
CREATE TEMP TABLE stacked AS
SELECT * FROM t1 UNION ALL BY NAME SELECT * FROM t2 UNION ALL BY NAME SELECT * FROM t3;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT count(*) FROM stacked) = 7
  AND (SELECT list((id, v) ORDER BY id) IS NOT DISTINCT FROM [(1, 'a'), (2, NULL), (3, 'c'), (4, NULL)] FROM result)
  AND (SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'v']) THEN 'as expected' ELSE error('the three tables hold 7 rows stacked and 4 distinct rows, with the repeated (1, a) and the (2, NULL) found in two tables each kept once, and the columns should keep the order of the first table') 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 three tables hold 7 rows stacked and 4 distinct rows, with the repeated (1, a) and the (2, NULL) found in two tables each kept once, and the columns should keep the order of the first table”.
  • 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).

Apache DataFusion (3)
  • UNIONread 2026-09-27

    Rows of both queries; DISTINCT by default, ALL keeps duplicates

  • union_by_name_distinctread 2026-09-27

    DataFrame method: Union by column name with duplicates removed

  • union_distinctread 2026-09-27

    DataFrame method: Union of two DataFrames with duplicates removed

Duckle (1)
  • Unionread 2026-09-25

    combine inputs and drop duplicates

Background

In Structured Query Language (SQL), a plain UNION automatically removes duplicate records from the combined result unless UNION ALL is used.[1] In PostgreSQL, UNION eliminates duplicate rows from its result in the same way as DISTINCT.[2]

When to use it

Use it when the inputs overlap, such as two extracts that both carry some of the same records, and one copy of each record is what the result should hold. (Meridian's view) Before relying on it, tidy or drop columns such as load timestamps that differ between otherwise identical copies of a record. (Meridian's view) In PostgreSQL's DISTINCT, two rows are considered distinct if they differ in at least one column value, and null values are considered equal in this comparison.[3]

In SQL, UNION ALL may be much faster than a plain UNION.[1] Where two versions of a record differ and one should win, removing exact duplicates is the wrong tool; keep one row per key with a rule that names the survivor. (Meridian's view)

The method

DuckDB's plain UNION follows set semantics and performs duplicate elimination, so only unique rows are included in the result.[4] In DuckDB, UNION BY NAME also follows set semantics and eliminates duplicates, whereas UNION ALL BY NAME follows bag semantics.[4]

Compared with the alternatives

In PostgreSQL, SELECT DISTINCT eliminates duplicate rows from one query's result table, and the key word ALL in its place keeps all rows, which is the default.[3] Removing repeats inside one table is a job for DISTINCT; a UNION earns its place when the copies to remove may sit in different tables. (Meridian's view)

Sources

  1. [1]Set operations (SQL) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: 7.4. Combining Queries (UNION, INTERSECT, EXCEPT)documentation
  3. [3]PostgreSQL: Documentation: 18: 7.3. Select Listsdocumentation
  4. [4]Set Operations – DuckDBdocumentation