# Merge sorted tables

Join / union

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

Interleave tables already sorted on a key into one table that stays sorted on that key.

Page: <https://meridian.online/operations/append-tables/merge-sorted-tables>

## Status

Runs as a SQL step.

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

## The DuckDB step

merge two sorted tables so the result stays sorted

```sql
-- both inputs are sorted on k, NULLs last
CREATE TEMP TABLE a AS SELECT * FROM (VALUES (1, 'a1'), (3, 'a2'), (3, 'a3'), (5, 'a4'), (NULL, 'a5')) t(k, v);
CREATE TEMP TABLE b AS SELECT * FROM (VALUES (2, 'b1'), (3, 'b2'), (4, 'b3'), (NULL, 'b4')) t(k, v);
-- stack, then order by the key; a tie keeps the first table's rows ahead of the second's
CREATE TEMP TABLE result AS
SELECT k, v FROM (SELECT 0 AS src, k, v FROM a UNION ALL SELECT 1, k, v FROM b) ORDER BY k NULLS LAST, src, v;
SELECT row_number() OVER () AS pos, * FROM result;
SELECT CASE WHEN ((SELECT list(v) = ['a1', 'b1', 'a2', 'a3', 'b2', 'b3', 'a4', 'a5', 'b4'] AND count(*) = 9 FROM result)
  AND (SELECT list(k) IS NOT DISTINCT FROM [1, 2, 3, 3, 3, 4, 5, NULL, NULL] FROM result)) THEN 'as expected' ELSE error('the merge should hold all 9 rows in key order 1, 2, 3, 3, 3, 4, 5, NULL, NULL with the rows of the first table first among equal keys') 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 Hop: [`Sorted Merge`](https://hop.apache.org/manual/latest/pipeline/transforms/sortedmerge.html) (read 2026-09-27)
- Apache Hop: [`Sorted schema merge`](https://hop.apache.org/manual/latest/pipeline/transforms/sortedschemamerge.html) (read 2026-09-27)
- Pentaho Data Integration: [`Sorted Merge`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- Polars: [`DataFrame.merge_sorted`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- Polars: [`merge_sorted`](https://docs.pola.rs/api/python/stable/reference/functions.html) (read 2026-09-27)