What does UNION require of the two SELECT lists it combines?

## The compatibility contract Before a set operator — `UNION`, `UNION ALL`, `INTERSECT`, `EXCEPT` — can combine two queries, their result shapes must line up. The requirement has three parts. **1. Equal number of columns.** If the left query selects three columns and the right selects two, the statement is a syntax/semantic error and never runs. This is the first thing to check when a set-operator query refuses to compile. **2. Correspondence by position, not by name.** Column *n* of the left query pairs with column *n* of the right. Names play no part in the matching. This is the rule that produces the most confusing bugs, because a query can be perfectly legal and still wrong: ```sql -- Legal, and wrong: the columns are swapped SELECT first_name, last_name FROM employees UNION ALL SELECT last_name, first_name FROM contractors; ``` Both branches select two text columns, so nothing complains, and the result quietly interleaves surnames into a column labelled `first_name`. When the swapped columns have different types, the same mistake surfaces as a type error instead — which is the friendlier outcome. **3. Compatible types per pair.** Each corresponding pair must have a type the engine can resolve to a single common type for the output column. Numeric families combine naturally: pairing `INTEGER` with `NUMERIC` yields a numeric output column, and the integer values are widened. Pairing an integer column with a character column normally fails, with an error along the lines of "types integer and text cannot be matched". Engines differ in how aggressively they coerce — some are stricter than others — so the portable habit is to make the intent explicit with `CAST` rather than to rely on implicit conversion: ```sql SELECT id, CAST(amount AS DECIMAL(12,2)) AS amount FROM invoices UNION ALL SELECT id, CAST(amount AS DECIMAL(12,2)) AS amount FROM credit_notes; ``` ## Result column names What is the output column called when the two branches disagree? The standard only guarantees a name when the corresponding columns already agree on one; otherwise the column is effectively unnamed and engines fill in a name of their choosing. In practice the engines you are likely to use take the names from the **first** branch: ```sql SELECT emp_id AS person_id FROM employees UNION ALL SELECT contractor_id FROM contractors; -- result column: person_id ``` Don't lean on that. If downstream code or an `ORDER BY` refers to the name, put an explicit alias on every column of the first branch. That converts an implementation detail into something the query states for itself. ## Why the names matter beyond readability A set operation produces a new result whose columns are the *result's* columns, not the inputs'. An `ORDER BY` written after the operator therefore sees only those columns and may reference them only by result name or by ordinal position — you cannot sort by a column that exists in the second branch but is not in the output, and you generally cannot sort by an arbitrary expression over the inputs. Ordinal positions are the escape hatch that always works: ```sql SELECT emp_id AS person_id, hired_on FROM employees UNION ALL SELECT contractor_id, started_on FROM contractors ORDER BY 2 DESC; -- sorts the combined result by its second column ``` ## Filling gaps between branches When the two sources genuinely have different shapes, you make them match by padding the shorter select list with literals — typically `NULL` cast to the right type, plus a discriminator so you can tell the branches apart afterwards: ```sql SELECT order_id, customer_id, CAST(NULL AS DATE) AS shipped_on, 'pending' AS state FROM pending_orders UNION ALL SELECT order_id, customer_id, shipped_on, 'shipped' FROM shipped_orders; ``` The explicit `CAST(NULL AS DATE)` matters: an untyped `NULL` literal leaves the engine to guess the column's type, and guesses differ between engines. Casting states the type once and makes the query portable. ## The habits that prevent the bugs - List columns explicitly in both branches; never combine two `SELECT *` queries, because a column added to one table later silently changes the degree or the pairing. - Keep the two select lists visually aligned in the source, one column per line, so a positional mismatch is visible. - Alias the first branch's columns deliberately, since those names become the result's names. - Cast at the branch level when types differ, rather than trusting implicit coercion to behave the same everywhere. All of this applies identically to `INTERSECT` and `EXCEPT`; the compatibility rules belong to set operators as a family, not to `UNION` specifically.