# Append tables and drop duplicate rows

Join / union

A variant of [Append tables](https://meridian.online/operations/append-tables).

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

Page: <https://meridian.online/operations/append-tables/append-and-drop-duplicates>

## Status

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.

## What other tools call it

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

- Apache DataFusion: [`UNION`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`union_by_name_distinct`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27)
- Apache DataFusion: [`union_distinct`](https://docs.rs/datafusion/latest/datafusion/dataframe/struct.DataFrame.html) (read 2026-09-27)
- Duckle: [`Union`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)