⊔UNION

Vertical stack + deduplication

UNION runs two or more SELECT branches and returns their combined result as a single multiset, then removes duplicate rows at the whole-row level (same values in every column position). Branches must be union-compatible: same number of columns, with types the engine can implicitly cast or align per position.

Because dedup usually implies a hash or sort on the full projected row, UNION is often more expensive than UNION ALL. Reach for UNION when duplicates would be logically wrong or when you explicitly want set semantics; otherwise default to UNION ALL and dedupe narrowly with DISTINCT or window functions where needed.

SQL

Combine disjoint tables with identical column layout

SELECT tracking_code, 'DOMESTIC' AS lane
FROM shipments_domestic
WHERE ship_date = DATE '2026-04-10'
UNION
SELECT tracking_code, 'INTL' AS lane
FROM shipments_intl
WHERE ship_date = DATE '2026-04-10'
ORDER BY tracking_code;

✓Illustrative result set

tracking_code	lane
TRK-1001	DOMESTIC
TRK-1002	INTL

SQL

UNION removes duplicate whole rows

SELECT batch_id, 'IMPORT' AS source
FROM batches_file_a
UNION
SELECT batch_id, 'IMPORT' AS source
FROM batches_file_b;

✓Illustrative result set

batch_id	source
B-100	IMPORT

📐Column lists and ORDER BY

Names and types come from the first branch; ordering applies to the combined result.

Use the same column order in every branch, SQL matches by position, not by alias name from branch two.
Put ORDER BY (and LIMIT) on the outermost scope: either wrap the union in a subquery/CTE and sort outside, or rely on dialect rules that allow a trailing ORDER BY after the last branch (supported on PostgreSQL, SQL Server, etc. verify for your engine).
Align types explicitly when branches differ slightly, e.g. CAST(domestic_id AS text) vs international string ids.

Padding different schemas with NULL placeholders

Branch one has five columns, branch two reorders or forgets a NULL in the third slot, silent wrong data or a runtime type error.

Design a stable union view or staging table

List every column explicitly in each branch. For recurring patterns, create a view or typed staging table so CI or migrations enforce shape.

When UNION is justified

Merging two event sources where the same business key might appear in both and only one output row should survive, or combining permission sets where duplicates are meaningless.

When UNION ALL wins

Appending daily partitions you know are disjoint, log streams where multiplicity matters, or staging loads where downstream dedup handles idempotency keys.

⚡ Advanced / dialect note

Some engines treat UNION between large branches as “sort entire union keys”: if you only need unique ids, UNION ALL followed by SELECT DISTINCT id FROM (…) t can sometimes be cheaper and clearer about where dedup happens, measure both.

Testing

For each branch, record COUNT(*) before union, COUNT(*) after UNION ALL, and after UNION. The deltas tell you how many duplicates you paid to remove, sanity-check that against business expectations.

TL;DR

UNION (without ALL) concatenates two SELECT results and deduplicates entire rows: usually implemented via sort or hash, so it costs more than UNION ALL. Column lists must match in arity and compatible types. If you only need “distinct from branch A OR branch B” at key grain, sometimes UNION ALL plus an outer GROUP BY is clearer and cheaper.