Fix “Different Number of Columns” in a SQL UNION
Each SELECT in a UNION has to return the same number of columns, in the same order.
The symptom
You combine two queries with UNION or UNION ALL and the database refuses, saying the used SELECT statements have a different number of columns. One side returns one column and the other returns two.
Why it happens
A set operation stacks rows from both queries into one result. For that to work the two row shapes have to match, so the database checks the column count before running either side. The same rule applies toUNION ALL, INTERSECT, and EXCEPT.
Column names do not have to match, and only the first query's names are kept. The count does have to match, and so does the position: the first column of one query lines up with the first column of the other, whatever they are called.
The fix
Decide what one row of the combined result should hold, then make both sides produce exactly that. Here the goal is a single list of names, so the two customer columns are joined into one before the union.
If a column genuinely has no counterpart on the other side, supply a literal such as null as last_name to keep the positions aligned rather than dropping a column that carries meaning.
Matching the count is not enough on its own. The columns also have to be type-compatible position by position, so a date column stacked onto an integer column fails with Conversion Error: Unimplemented type for cast (INTEGER -> DATE) instead. Check that the first column of each query means the same thing.
How other engines word it
| Engine | Message |
|---|---|
| DuckDB | Binder Error: Set operations can only apply to expressions with the same number of result columns |
| MySQL | The used SELECT statements have a different number of columns (error 1222) |
| PostgreSQL | each UNION query must have the same number of columns |
| SQL Server | All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. |
| Oracle | ORA-01789: query block has incorrect number of result columns |
| SQLite | SELECTs to the left and right of UNION ALL do not have the same number of result columns |