# 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. Source: https://docs.querylab.io/bigquery-errors/queries-in-union-all-have-mismatched-column-count/ Updated: 2026-11-04 ```text 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 ```sql 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 ``` ```text 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: ```sql 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`. ```sql 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: ```sql 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: | Modifier | A column that only one query has | | ------------------------------ | ---------------------------------------------------------------- | | `UNION ALL BY NAME` | Error: both queries must have the same column names | | `INNER UNION ALL BY NAME` | Left out of the result | | `FULL OUTER UNION ALL BY NAME` | Kept, with `NULL` for the query that lacks it | | `LEFT OUTER UNION ALL BY NAME` | Kept if the first query has it, left out if only the second does | With plain `UNION ALL BY NAME` on these tables BigQuery says: ```text 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: ```text 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: ```sql 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](https://docs.querylab.io/bigquery/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. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Set operators](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set_operators) - [BY NAME or CORRESPONDING](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#by_name_or_corresponding)