Join / union · All operations

Append tables

Append tables by rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

one table from two or more

Level

one clause of SQL

The DuckDB step

Append tables whose columns differ

It reads the orders and customers tables the proofs share. Create them first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL

CREATE TEMP TABLE result AS
SELECT customer, region FROM orders UNION ALL BY NAME SELECT customer, segment FROM customers;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 14 AND count(region) = 9 AND count(segment) = 4 FROM result)) THEN 'as expected' ELSE error('the two tables did not append to 14 rows with each column filled from its own table') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the two tables did not append to 14 rows with each column filled from its own table”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Append tables and drop duplicate rows
Stack the rows of two or more tables and keep only one copy of each distinct row.
Merge sorted tables
Interleave tables already sorted on a key into one table that stays sorted on that key.
Append tables with a source column
Stack the rows of several tables and add a column that records which table each row came from.

Stack tables by rows

Stack the rows of two or more tables into one, matching columns by name or by position and keeping every row.

Append many files or shards

Combine the rows of many files, messages or sharded tables picked by a pattern or a grouping rule into one table.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)

Unionread 2026-09-27
Stack two or more datasets by column name or position
Union In-DBread 2026-09-27
Stack two or more in-DB streams by field name or position inside the database

Apache DataFusion (7)

UNIONread 2026-09-27
Pipe: union with other queries, BigQuery-style |> syntax
UNIONread 2026-09-27
Rows of both queries; DISTINCT by default, ALL keeps duplicates
UNION BY NAMEread 2026-09-27
Union matching columns by name instead of position
unionread 2026-09-27
DataFrame method: Union of two DataFrames, keeping duplicates
union_by_nameread 2026-09-27
DataFrame method: Union by column name, keeping 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

Apache Hop (4)

Append Streamsread 2026-09-27
Append one stream after another, keeping the first stream entirely before the second
Sorted Mergeread 2026-09-27
Merge several key-sorted streams into one sorted stream
Sorted schema mergeread 2026-09-27
Merge pre-sorted streams of differing layouts into one sorted stream with a union schema
Stream schema mergeread 2026-09-27
Union streams of differing layouts by field name into one combined layout

Apache NiFi (2)

MergeContentread 2026-09-27
Merge a group of FlowFiles into a single FlowFile by a user-defined strategy
MergeRecordread 2026-09-27
Merge record-oriented FlowFiles into one FlowFile holding all their records, by binning

Apache SeaTunnel (1)

TableMergeread 2026-09-27
Merge sharded tables of a multi-table stream into one named table

AWS Glue (2)

Unionread 2026-09-27
Combine the rows of several sources that share a schema
unionread 2026-09-27
Union of two frames by rows

Azure Data Factory (1)

Unionread 2026-09-27
Combine several streams by rows

dbt-utils (1)

union_relationsread 2026-09-27
UNION ALL a list of relations, aligning columns by name, null-filling gaps, adding a source column

Duckle (3)

Merge Streamsread 2026-09-25
concatenate input streams with UNION ALL
Unionread 2026-09-25
combine inputs and drop duplicates
Union Allread 2026-09-25
combine inputs and keep all rows

EasyMorph (1)

Append another tableread 2026-09-27
Append another table's rows below, or its columns beside, the current table

Ibis (1)

Unionread 2026-09-27
Stack two tables, keeping or removing duplicate rows

KNIME Analytics Platform (2)

Concatenateread 2026-09-27
Stack two or more tables row-wise
DB Concatenateread 2026-09-27
In-database: union two database tables row-wise

Pentaho Data Integration (3)

Append streamsread 2026-09-27
Append two streams in a fixed order
Prioritize streamsread 2026-09-27
Read several input streams in a set priority order into one
Sorted Mergeread 2026-09-27
Merge several key-sorted input streams into one sorted stream

Polars (6)

DataFrame.extendread 2026-09-27
Append the rows of another frame into this frame's own memory, in place
DataFrame.merge_sortedread 2026-09-27
Interleave two frames already sorted on a key into one sorted frame
DataFrame.vstackread 2026-09-27
Append the rows of another frame with the same schema
concatread 2026-09-27
Combine frames: vertical, diagonal, horizontal or aligned on keys, with relaxed type-unifying forms
merge_sortedread 2026-09-27
Interleave several frames already sorted on a key into one sorted frame
unionread 2026-09-27
Combine frames as concat does, with no guarantee on the order of the output rows

Power Query M (2)

Table.Combineread 2026-09-27
Append a list of tables into one; columns are the union of the inputs unless a column set is given
Table.FromPartitionsread 2026-09-27
Append partitioned tables into one, adding a column that carries each partition's value

RapidMiner Studio (5)

Appendread 2026-09-27
Append two or more tables with the same columns by rows
Append (Robust)read 2026-09-27
Append tables by rows, keeping nominal values known only from metadata
Append (Superset)read 2026-09-27
Append tables by rows even when their columns or column types differ
Unionread 2026-09-27
Append two tables by rows, keeping the union of their columns
Union (In Database)read 2026-09-27
Append a second in-database input to the first by rows

SQLMesh (1)

@UNIONread 2026-09-27
UNION ALL or DISTINCT several tables over columns with matching names and types, optionally conditional

Tableau Prep (2)

Unionread 2026-09-27
Append the rows of up to 10 inputs, matching fields by name
Union files and database tables in the Input stepread 2026-09-27
Union files of a directory, or tables of a database, matched by wildcard and file filters

Talend (1)

tUniteread 2026-09-27
Append the rows of several flows that share a schema into one flow

Trifacta (1)

Union datasetsread 2026-09-27
Add rows from other datasets

VisiData (9)

join-cols-appendread 2026-09-23
Join on selected columns: stack rows of all sheets; columns from all sheets by name
join-cols-concatread 2026-09-23
Join on selected columns: stack rows of all sheets; columns and sheet type of the first
join-selected-appendread 2026-09-23
Join selected sheets: stack rows of all sheets; columns from all sheets by name
join-selected-concatread 2026-09-23
Join selected sheets: stack rows of all sheets; columns and sheet type of the first
join-sheets-all-appendread 2026-09-23
Join all stacked sheets: stack rows of all sheets; columns from all sheets by name
join-sheets-all-concatread 2026-09-23
Join all stacked sheets: stack rows of all sheets; columns and sheet type of the first
join-sheets-top2-appendread 2026-09-23
Join top two sheets: stack rows of all sheets; columns from all sheets by name
join-sheets-top2-concatread 2026-09-23
Join top two sheets: stack rows of all sheets; columns and sheet type of the first
s3-join-rowsread 2026-09-23
Open the selected S3 entries and append their rows into one sheet

Background

In Structured Query Language (SQL), UNION combines the results of two queries into a single table, and UNION ALL keeps every row, duplicates included.^[1] In PostgreSQL, UNION effectively appends the result of the second query to the result of the first, with no guarantee that rows are returned in that order.^[2] The SQL set operators UNION, INTERSECT and EXCEPT correspond mathematically to the concepts of union, intersection and set difference.^[1]

When to use it

UNION can be useful in data warehouses, for example to combine a sales2005 table and a sales2006 table that share a structure but were kept apart for performance.^[1] In DuckDB, read_parquet given a list of files treats them as a single table, and a glob pattern in a file name reads every file that matches it.^[3] Reach for an append when the inputs record the same kind of thing, one row per event or entity, and differ only in which slice of time, place or source they cover. (Meridian's view)

In PostgreSQL, the queries combined by UNION must be union compatible, returning the same number of columns with compatible data types.^[2] In DuckDB, traditional set operations line columns up by position, and the result takes its column names from the first query.^[4] DuckDB also offers UNION BY NAME, which matches columns by name and fills a column missing from one query with NULL values.^[4] In Tableau Prep, a union of tables whose fields do not match produces extra fields, which have to be merged to resolve the mismatch.^[5]

In SQL, UNION ALL does not guarantee the order of rows, so rows from the second operand may appear before, after or mixed with the first operand's rows.^[1] Where a specific order of the combined rows is wanted, the query needs an ORDER BY.^[1] Check the row count of the result against the sum of the inputs' counts: an append that keeps every row should match it exactly. (Meridian's view)

The method

In DuckDB, UNION ALL returns all rows of both queries following bag semantics, without duplicate elimination.^[4] In DuckDB, UNION BY NAME does not require the inputs to have the same number of columns.^[4] In DuckDB, a UNION casts implicitly to one of the returned types where columns differ in type, and throws an error if that is not possible.^[4]

In Polars, concat with the diagonal strategy finds a union between the column schemas and fills missing column values with null.^[6] In Power Query M, Table.Combine appends a list of tables, and the result's columns are a union of the inputs' types unless a set of columns is given.^[7]

Compared with the alternatives

In PostgreSQL, INTERSECT returns the rows found in both queries' results and EXCEPT returns the rows of the first query that are not in the second.^[2] In Polars, concat with the horizontal strategy stacks frames side by side and requires all input frames to have the same height.^[6]

Sources

[1]Set operations (SQL) - Wikipediasecondary source
[2]PostgreSQL: Documentation: 18: 7.4. Combining Queries (UNION, INTERSECT, EXCEPT)documentation
[3]Reading Multiple Files – DuckDBdocumentation
[4]Set Operations – DuckDBdocumentation
[5]Aggregate, Join, or Union Data - Tableaudocumentation
[6]polars.concat — Polars documentationdocumentation
[7]Table.Combine - PowerQuery M | Microsoft Learndocumentation

Found by dovetail

dovetail’s dovetail relate proposes combining tables whose names differ by one token (a year, a date, a number or a word) and whose columns agree into one dataset.
Evidence: docs/table-roles.md in dovetail at c5c1a4f

Used in

2 Protocols use this operation:

EDGAR ↔ GLEIF — Company-to-LEI Crosswalk, step tier
Online Retail II dataset, step retail_lines