📎UNION ALL

Concatenate bags, keep multiplicity

UNION ALL appends the rows from each SELECT branch in order without removing duplicates. It is the multiset version of union: if the same tuple appears in two branches, you will see it twice. That matches event logs, time-ordered CDC streams, and partition-by-day ELT where each slice is already disjoint by construction.

Runtime cost is usually dominated by scanning each branch and streaming rows forward, no global dedup pass. Downstream consumers must know whether they are allowed to see duplicates (idempotent sinks) or must dedupe on keys.

Operator	Deduplicates?	Typical cost driver
UNION	Yes (set semantics)	Hash or sort on full row width across branches
UNION ALL	No	Sequential scans + append; global ORDER BY may still sort everything

SQL

UNION collapses identical rows

SELECT 'A' AS tag
UNION
SELECT 'A' AS tag;

✓Illustrative result set

tag
A

SQL

UNION ALL preserves duplicates

SELECT 'A' AS tag
UNION ALL
SELECT 'A' AS tag;

✓Illustrative result set

tag
A
A

📅Partition-style merges

Classic warehouse pattern: one SELECT per day table, UNION ALL into a reporting view.

When rerunning a job for day d, delete or truncate only that day’s slice first, then INSERT … SELECT from the union of micro-batches. Consumers query the view with a date predicate so the planner prunes irrelevant branches where metadata allows.

SQL

Stack heterogeneous sources with aligned columns

SELECT run_id, 'LIMS' AS system, recorded_at FROM lims_events WHERE recorded_at >= DATE '2026-04-01'
UNION ALL
SELECT run_id, 'INSTRUMENT' AS system, recorded_at FROM instrument_events WHERE recorded_at >= DATE '2026-04-01'
ORDER BY recorded_at, run_id;

✓Illustrative result set

run_id	system	recorded_at
R-1	LIMS	2026-04-01 06:01
R-1	INSTRUMENT	2026-04-01 06:02
R-2	INSTRUMENT	2026-04-01 07:40

Overlapping time windows in two branches

Two UNION ALL branches both include 00:00–23:59 for the same logical day after a clock skew fix, downstream “count events per day” doubles.

Partition by non-overlapping keys or dedupe with windows

Gate each branch with mutually exclusive predicates, or load into a staging table with a primary key on (source, event_id) and use ON CONFLICT DO NOTHING / merge semantics.

Logs and traces

Request spans across services: each service emits rows; UNION ALL preserves duplicate sequence numbers if retries re-emit, dedupe in the trace UI or with a window on (trace_id, span_id).

Global ORDER BY

Sorting the combined stream may require materializing all branches, if you only need “top 100 recent,” prefer merging sorted streams in application code or use LIMIT per branch then merge carefully.

⚡ Advanced / dialect note

In PostgreSQL, plain UNION can remove duplicates without sorting if the planner can prove disjoint branches, but do not rely on that proof surviving statistics changes; document intent in the keyword choice.

Contract with consumers

If an API exposes “merged timeline” SQL built with UNION ALL, state in the doc whether duplicates are possible and which stable sort keys clients should use for pagination.

TL;DR

UNION ALL concatenates result sets from two SELECTs without removing duplicates, fast and preserves multiplicity. Column counts and types must align across branches; ORDER BY applies to the combined output (often requires wrapping in a subquery for portability). Use when duplicates are expected or harmless; prefer UNION (distinct) only when you truly need deduplication.