Docs/Error messages/UNION column count mismatch
Error messagesTypes

Queries in UNION ALL have mismatched column count

Every query in a UNION, INTERSECT or EXCEPT must return the same number of columns, matched by position. The message gives the count of the first query and of the one that differs, and the position points at that query.

Practice this in a free lesson on learn.querylab.io: UNION ALL and set operations.

Queries in UNION ALL have mismatched column count; query 1 has 3 columns, query 2 has 2 columns at [5:1]

What BigQuery means

A set operation puts the rows of one query under the rows of another. For that to work, both queries must return the same number of columns. BigQuery pairs the columns by position: the first column of the first query with the first column of the second, and so on. It does not look at the names.

The message gives two counts. query 1 is always the first query of the set operation. The second number is the position of the query that differs, and at [5:1] is the line and column where that query starts. With three or more queries, it tells you which one. BigQuery checks this when it compiles the query, so a dry run reports it and nothing is read or billed.

The smallest query that reproduces it

WITH web AS (SELECT 1 AS order_id, 'web' AS channel, 20 AS amount),
store AS (SELECT 2 AS order_id, 15 AS amount)
SELECT order_id, channel, amount FROM web
UNION ALL
SELECT order_id, amount FROM store
Queries in UNION ALL have mismatched column count; query 1 has 3 columns, query 2 has 2 columns at [5:1]

web has a channel column and store does not.

The fixes

The examples below use the same two tables, web and store.

Add the missing column to the shorter query. Write a constant or a NULL in the same position:

SELECT order_id, channel, amount FROM web
UNION ALL
SELECT order_id, 'store' AS channel, amount FROM store
ORDER BY order_id

This returns 1, web, 20 and 2, store, 15.

SELECT order_id, channel, amount FROM web
UNION ALL
SELECT order_id, NULL, amount FROM store
ORDER BY order_id

The second row has NULL in channel. A bare NULL is accepted in this position.

Drop the extra column from the longer query. If you do not need channel, remove it from the first query. Both queries then return order_id and amount.

Match columns by name. FULL OUTER UNION ALL BY NAME pairs columns by name, and fills NULL where a query has no such column:

SELECT * FROM web
FULL OUTER UNION ALL BY NAME
SELECT * FROM store
ORDER BY order_id

The result has order_id, channel and amount, and channel is NULL for the store row. The other modifiers treat a column that only one query has in their own way:

ModifierA column that only one query has
UNION ALL BY NAMEError: both queries must have the same column names
INNER UNION ALL BY NAMELeft out of the result
FULL OUTER UNION ALL BY NAMEKept, with NULL for the query that lacks it
LEFT OUTER UNION ALL BY NAMEKept if the first query has it, left out if only the second does

With plain UNION ALL BY NAME on these tables BigQuery says:

BY NAME requires all input queries to have identical column names, but query 1 has [order_id, channel, amount] and query 2 has [order_id, amount] at [4:1]

INNER UNION ALL BY NAME returned order_id and amount only. LEFT OUTER UNION ALL BY NAME with web first returned all three columns, and with store first returned order_id and amount.

The variants of the message

Each line was produced by BigQuery. Only the operator and the counts change:

Queries in UNION DISTINCT have mismatched column count; query 1 has 3 columns, query 2 has 2 columns at [5:1]
Queries in INTERSECT DISTINCT have mismatched column count; query 1 has 2 columns, query 2 has 1 column at [1:42]
Queries in EXCEPT DISTINCT have mismatched column count; query 1 has 2 columns, query 2 has 1 column at [1:39]
Queries in UNION ALL have mismatched column count; query 1 has 1 column, query 3 has 2 columns at [8:1]

The last one comes from three queries joined with UNION ALL. Query 1 and query 2 agreed, and the third differed.

SELECT * hides the counts. SELECT * FROM web UNION ALL SELECT * FROM store gives the same error, with query 1 has 3 columns, query 2 has 2 columns. When a table gains a column later, a query that worked can start to fail this way.

Queries that look wrong but run

Columns in a different order. When the counts match, BigQuery pairs by position and does not check names. If two queries return the same types in a different order, the values land in the wrong columns:

WITH a AS (SELECT 1 AS order_id, 20 AS amount),
b AS (SELECT 15 AS amount, 2 AS order_id)
SELECT * FROM a
UNION ALL
SELECT * FROM b

The rows are 1, 20 and 15, 2. The second row has an amount of 2. List the columns in the same order in both queries, or use UNION ALL BY NAME, which returns 1, 20 and 2, 15.

Different names. SELECT 1 AS a UNION ALL SELECT 2 AS b runs. The result column is called a, because the result takes its names from the first query.

Different errors that look like this one

Column 1 in UNION ALL has incompatible types: INT64, STRING at [1:25]. The counts match, but the type of a column does not. The number is the position of the column. See Set operations.

In Querylab.io

The editor does not mark a column count mismatch as you type. BigQuery’s dry run reports it, and Querylab.io shows that error when you pause typing. The marker sits on the first character of the query that differs, the position in the message. A failed run puts the same message in the status bar. There is no one-click fix for this error. Add or remove the column by hand.