UNION and UNION ALL

Concepts covered: Set Combination

UNION stacks the result of 1 query on top of another into a single result set. The 2 queries must return the same number of columns, which are matched by position. Each position must hold compatible types. PostgreSQL and SQL Server reject an INTEGER over a TEXT, but MySQL and SQLite coerce it silently and hide the mistake. An INTEGER over a DECIMAL is widened. The output takes its column names from the first query, so names in the second are ignored. What the operator does with a row that appears in both inputs is the choice between UNION and UNION ALL.

UNION removes duplicates

Plain UNION is shorthand for UNION DISTINCT: after stacking, the engine removes any output row identical to another across every column. The animation stacks 3 customers on 3 suppliers, and Cara, id 3, is in both tables.

SELECT id, name FROM customers
UNION
SELECT id, name FROM suppliers

The union takes 6 input rows and returns 5. Cara appeared once in each input and survives once, because the duplicate check compares whole rows and (3, Cara) matches (3, Cara). A supplier named Cara with id 6 would have survived alongside her, since the rows would differ in a column. The removal is also not limited to rows from different inputs: 2 identical rows inside customers alone would collapse to 1.

UNION ALL keeps everything

UNION ALL stacks and stops. No row is compared with any other, so the output row count is the sum of the inputs.

SELECT id, name FROM customers
UNION ALL
SELECT id, name FROM suppliers

All 6 rows come out, and Cara appears twice. The difference in cost is larger than the difference in rows suggests. To remove duplicates the engine sorts or hashes the full width of the result, so it has to see every row before it emits any; UNION ALL streams rows through as they arrive. On 2 inputs of 50 million rows each, the distinct pass is the most expensive step in the query, and it's paid even when the inputs share no rows at all.

Orders are archived by year into orders_2023 and orders_2024. The 2 tables have identical columns, and every order id lives in exactly 1 of them. A report needs both years in one result. Which operator?

• UNION ALL, because no row can appear in both tables, so a duplicate pass would remove nothing and cost a full sort
• UNION, because removing duplicates costs nothing when there are none
• UNION DISTINCT, because it also drops orders that were cancelled in both years
• An inner JOIN on order_id

Tagging the source

Once rows are stacked, nothing in them records which query they came from. A literal in each SELECT list fixes that, and because columns match by position the literal must sit in the same slot in both queries.

SELECT id, name, 'customer' AS party_type FROM customers
UNION ALL
SELECT id, name, 'supplier' AS party_type FROM suppliers
ORDER BY id

The ORDER BY at the end sorts the stacked result and applies to the whole union, never to the second SELECT alone; a query that needs each input kept together sorts the final result by party_type first. LIMIT behaves the same way and caps the combined result. The practice database splits notification sends between push_notifs and push_notifs_2fa. Both tables have the same columns, which is the shape the next widget stacks.

Build one list of every notification sent, from both push_notifs and push_notifs_2fa, keeping every row even when 2 are identical, newest sends first.

About this lesson

This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.