Lesson 31 / 37 · Composing queries

UNION stacks two results

Each side is its own pipeline. UNION ALL keeps every row; plain UNION would also remove duplicate rows across both sides. Columns are matched by position, and the names come from the left side.

The query

SELECT name, 'customer' AS kind
FROM customers
WHERE country = 'US'
UNION ALL
SELECT name, 'product' AS kind
FROM products
WHERE category = 'merch'
ORDER BY kind, name

Try UNION without ALL: the same 4 rows, because UNION removes only rows that are identical in every column, and no row is on both sides.

Next: A recursive CTE runs in rounds · All lessons