Stack rows, don't glue columns

A join glues tables horizontally (adding columns). Set operations stack or compare them vertically (combining rows). As a DE you reach for them constantly: stacking multi-region loads into one stream, or diffing yesterday's IDs against today's to find what disappeared.

The four operators, all requiring both sides to have the same number of columns with compatible types:

Operator	Meaning
`UNION ALL`	stack all rows from both, keep duplicates (cheapest: no dedup pass)
`UNION`	stack and remove duplicate rows
`INTERSECT`	rows present in both result sets
`EXCEPT`	rows in the first set but not the second (a set difference / diff)

Worked example: stack two regional order feeds

SELECT order_id, total_cents FROM orders_eu
UNION ALL
SELECT order_id, total_cents FROM orders_us;

Anatomy:

SELECT a, b FROM left_source
UNION ALL                       ← operator sits BETWEEN two full SELECTs
SELECT a, b FROM right_source   ← same column count, compatible types, matched by POSITION
ORDER BY a                      ← a single ORDER BY applies to the whole combined result, at the end

Columns are matched by position, not by name: the first column of the top query lines up with the first column of the bottom, regardless of what they're called. The output takes its column names from the first SELECT.

`UNION` vs `UNION ALL`: a real cost decision

UNION runs a deduplication pass (effectively a sort or hash) over the combined rows; UNION ALL just concatenates. Note that UNION dedupes on the entire row, not on one key column: in the demo above, EU order 101 and US order 201 both have total_cents = 5000, yet swapping UNION ALL for UNION drops nothing and returns the same 5 rows, because their order_ids differ. A row disappears only when every selected column matches, such as the same order arriving in both regional feeds under the same order_id and total. When you know the sources don't overlap, or you want to keep such duplicates (two identical rows can be two real events), use UNION ALL. Reaching for UNION by reflex silently drops legitimate duplicate rows and costs more.

Keep it readable / common pitfall

Put ORDER BY only once, after the final SELECT. It sorts the whole combined set. An ORDER BY inside an individual branch is either ignored or an error depending on the engine.

Recap. Set operators combine rows vertically by column position: UNION ALL stacks and keeps dupes (cheapest), UNION dedupes, INTERSECT keeps common rows, and EXCEPT computes a diff.

CREATE TABLE orders_eu ( order_id INTEGER, total_cents INTEGER ); CREATE TABLE orders_us ( order_id INTEGER, total_cents INTEGER ); INSERT INTO orders_eu VALUES (100, 2500), (101, 5000), (102, 9900); INSERT INTO orders_us VALUES (200, 1500), (201, 5000);

Your turn

The task this lesson builds to.

Combine two regional order tables into one stream, keeping every row (including any coincidental duplicates). Return order_id and region for all EU and US orders, using UNION ALL, sorted by order_id, then region.

3 hints and 1 automated check are waiting in the workspace.

Make it stick

A second problem on the same idea, so it survives past today.

Diff two source extracts. Find customer IDs that were present in yesterday's extract but are missing from today's (dropped customers) using EXCEPT. Return a single column dropped_customer_id, sorted ascending. (Both extracts may contain duplicate rows within themselves. EXCEPT treats each side as a set, which is exactly what you want for a presence diff.)