Level 6 · The rules of a UNION

Every set operation, UNION ALL, UNION, INTERSECT and EXCEPT, follows the same small set of rules. They are easy to state and easy to forget when a query grows to five branches.

Rule 1: same number of columns

Each SELECT must return the same number of columns. This fails before any data is read:

SELECT customer_id, email FROM customers
UNION ALL
SELECT employee_id FROM employees;
-- Binder Error: Set operations can only apply to expressions
-- with the same number of result columns

If one source lacks a column, fill the gap explicitly with NULL AS email or a constant.

Rule 2: matched by position, types must be compatible

The database pairs the first column of every branch, then the second, and so on. Names play no part. The types in each position must be compatible: stacking signup_date (DATE) on salary (INTEGER) is an error. Compatible but different types, such as an integer and a decimal, are widened to the larger type.

The dangerous case is two columns of the same type in swapped order, for example units, revenue in one branch and revenue, units in the other. Nothing errors, and the result is wrong. Line up your branches visually, one column per line, so a swap is easy to see.

Rule 3: names come from the first SELECT

The result’s columns are named after the first SELECT. Aliases in later branches are ignored, which is why the first branch should carry all the aliases and the others can skip them. Refer to those first-branch names in the final ORDER BY.

Rule 4: one ORDER BY, at the end

ORDER BY (and LIMIT) after the last SELECT apply to the whole combined result. To sort or limit a single branch, wrap it in parentheses or a subquery first.

On Harbor & Pine, the query in the quiz stacks 2,018 customers and 60 employees into 2,078 rows, sorted by id, so customer 1 and employee 1 sit next to each other. The kind column is what keeps them apart.

Common mistakes

• Assuming matching aliases are required, and renaming columns in every branch “to be safe”.
• Sorting by a later branch’s alias. DuckDB tolerates it, but Postgres and SQL Server expect the first SELECT’s names, so code that relies on it breaks when it moves.
• Letting the engine cast silently. DuckDB will stack the number 1 on the text 'x' by turning both into text. Other databases raise an error. Cast explicitly so the intent is clear.

In SQL Server

The same four rules apply. SQL Server is stricter on types: stacking an INT on a non-numeric text value fails with a conversion error. Text labels are often built with +, for example 'Customer ' + CAST(customer_id AS varchar(10)), where DuckDB uses ||.