UNION: queries must have the same number of columns

Every SELECT combined with UNION, INTERSECT, or EXCEPT has to return the same number of columns, in a compatible order. Here's the fix and the type-compatibility trap that comes right after it.

• SQL Server: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
• SQLite: SELECTs to the left and right of UNION do not have the same number of result columns
• PostgreSQL: ERROR: each UNION query must have the same number of columns
• MySQL: The used SELECT statements have a different number of columns

Loading editor…

The cause

UNION (and INTERSECT, EXCEPT) stacks the results of two SELECTs into one result set — which only makes sense if both sides have the same shape: same number of columns, in an order where each pair is a compatible type. The left side selects two columns here; the right side selects one.

The fix — match the column count and order

Loading editor…

Columns are matched by position, not by name — the result's column headers come from the first SELECT regardless of what the second one calls them. If you need a placeholder to make the counts match, select a literal:

Loading editor…

The next error waiting after this one: type mismatch

Once the column counts agree, engines also expect each paired column to be a compatible type. Stack a text column against a numeric one and some engines error, others silently coerce — which is its own trap, since a query that "works" may just be quietly converting everything to text or truncating a number. Keep types aligned on purpose:

Loading editor…

UNION vs UNION ALL

UNION also de-duplicates the combined result, which means a hidden sort or hash step on every query — often unwanted, and wasteful if the two sides can't produce duplicates anyway. If you don't need de-duplication, UNION ALL skips it and is the better default:

Loading editor…