“Each UNION Query Must Have the Same Number of Columns”

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 to UNION 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.

select product_name
from products
union all
select first_name, last_name
from customers

select product_name as name
from products
union all
select first_name || ' ' || last_name as name
from customers

In DuckDB: match columns by name

DuckDB also has UNION ALL BY NAME (and UNION BY NAME). It lines columns up by name instead of by position, and fills a column one side doesn't have with NULL, so the two sides may return different numbers of columns:

select product_name as name, 'product' as kind
from products
union all by name
select first_name || ' ' || last_name as name
from customers

Every customer row gets NULL in kind, because the customers query has no column by that name. A column spelled differently on each side, such as name and full_name, becomes two columns, each half empty. PostgreSQL, MySQL, and SQL Server don't accept BY NAME. In those engines, write the missing column as null as kind and keep the positions aligned.

How other engines word it

Related errors

• Fix “Referenced Column Not Found” in SQL

  No table in the FROM clause has a column by that name. Usually a typo or a table you did not join.

• Fix “Syntax Error at or Near” in SQL

  The parser stopped at the first word it could not use. The quoted token is where it gave up, not always where the mistake is.

Learn more

• Lesson 15.2 · UNION
• Lesson 15.1 · UNION ALL
• Reference · Set operations
• Mistake · Why UNION ALL keeps duplicate rows