# Syntax error: Expected X but got Y

> BigQuery was in the middle of a construct and the next token was not one it could accept. The message lists what it would have accepted. The usual causes are a UNION with no ALL or DISTINCT, a bracket that is never closed, and a missing keyword such as BY or AS.

Source: https://docs.querylab.io/bigquery-errors/syntax-error-expected-but-got/

Updated: 2026-10-30

```text
Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [3:1]
```

## What BigQuery means

The parser was part way through a construct, such as a `UNION` or a function call, and the next token could not continue it. This message says which tokens would have worked (`Expected ...`) and which one it found (`but got ...`). `[3:1]` is line 3, column 1: the token it found.

Both halves are useful. The expected list names the construct. `ALL or DISTINCT` is a set operator, `")"` is an open bracket, `BY` is an `ORDER` or `GROUP` without its `BY`. The `got` part is the token that came instead, which is often the start of the next clause. The mistake is normally just before it.

When the statement was complete and extra text followed, the message is a different one: `Expected end of input`. See [Expected end of input](https://docs.querylab.io/bigquery-errors/syntax-error-expected-end-of-input/).

## The smallest query that reproduces it

```sql
SELECT 1 AS a
UNION
SELECT 2 AS a
```

```text
Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [3:1]
```

## The fixes

### UNION, INTERSECT with no ALL or DISTINCT

BigQuery does not accept a bare `UNION`. Say whether duplicates stay:

```sql
SELECT 1 AS a
UNION ALL
SELECT 2 AS a
```

```sql
SELECT 1 AS a
UNION DISTINCT
SELECT 2 AS a
```

`UNION ALL` keeps every row and does less work, because rows are not compared. `UNION DISTINCT` removes duplicate rows. `INTERSECT` gives the same error and works with `INTERSECT DISTINCT`. `EXCEPT` has its own message:

```sql
SELECT 1 AS a
EXCEPT
SELECT 2 AS a
```

```text
EXCEPT must be followed by ALL, DISTINCT, or "(" at [2:1]
```

`EXCEPT DISTINCT` works.

In a chain, the error is at the bare `UNION`:

```sql
SELECT 1 AS a
UNION ALL
SELECT 2 AS a
UNION
SELECT 3 AS a
```

```text
Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [5:1]
```

A chain must use one operator throughout. If you change from `UNION ALL` to `UNION DISTINCT` partway, you get another message:

```sql
SELECT 1 AS a
UNION ALL
SELECT 2 AS a
UNION DISTINCT
SELECT 3 AS a
```

```text
Syntax error: Different set operations cannot be used in the same query without using parentheses for grouping at [4:1]
```

Group the operation that goes first in brackets:

```sql
(SELECT 1 AS a UNION ALL SELECT 2 AS a)
UNION DISTINCT
SELECT 3 AS a
```

### A bracket that is never closed

```sql
SELECT COUNT(* FROM UNNEST([1])
```

```text
Syntax error: Expected ")" but got keyword FROM at [1:16]
```

The position is the first token after the point where the bracket should have closed. Count the brackets from the one that opens the call. Subqueries and `WITH` clauses give the same message:

```sql
SELECT 1 FROM (SELECT 1
```

```text
Syntax error: Expected ")" but got end of script at [1:24]
```

```sql
WITH t AS (SELECT 1 AS a SELECT a FROM t
```

```text
Syntax error: Expected ")" but got keyword SELECT at [1:26]
```

When the bracket holds a list, a comma would also have been fine, and the message says so:

```sql
SELECT 1 FROM UNNEST([1,2,3]
```

```text
Syntax error: Expected ")" or "," but got end of script at [1:29]
```

Square brackets work the same way: `SELECT [1, 2` gives `Expected "]" but got end of script at [1:13]`.

A bracket around a single value asks for a comma first, because `(a, b)` is also valid: `SELECT (1 + 2` gives `Expected "," but got end of script at [1:14]`.

### A missing comma in a list

Two values next to each other in an `IN` list:

```sql
WITH t AS (SELECT 1 AS a) SELECT a FROM t WHERE a IN (1 2)
```

```text
Syntax error: Expected "," but got integer literal "2" at [1:57]
```

### A keyword without its partner

`GROUP` and `ORDER` are always followed by `BY`, and a window's `PARTITION` too:

```sql
WITH t AS (SELECT 1 AS a) SELECT a FROM t GROUP a
```

```text
Syntax error: Expected @ for hint or keyword BY but got identifier "a" at [1:49]
```

The `@` is for a query hint, which is why it is in the list. `ORDER a` and `OVER (PARTITION a)` give the same text. A `SELECT 1 order` that ends there gives `Expected @ for hint or keyword BY but got end of script at [1:15]`.

`CAST` needs `AS`:

```sql
SELECT CAST(1 INT64)
```

```text
Syntax error: Expected keyword AS but got identifier "INT64" at [1:15]
```

`CASE` needs `WHEN`, and an `END`:

```sql
SELECT CASE 1 = 1 THEN 2 END
```

```text
Syntax error: Expected keyword WHEN but got keyword THEN at [1:19]
```

```sql
SELECT CASE WHEN 1 = 1 THEN 2 ELSE 3
```

```text
Syntax error: Expected keyword END but got end of script at [1:37]
```

An outer join needs `JOIN` after `LEFT`. BigQuery found `t` where `JOIN` had to be:

```sql
WITH t AS (SELECT 1 AS a) SELECT * FROM t LEFT t AS u ON TRUE
```

```text
Syntax error: Expected keyword JOIN but got identifier "t" at [1:48]
```

### A statement that stops early

```sql
SELECT 1 AS a
UNION ALL
```

```text
Syntax error: Expected "(" or keyword SELECT or keyword TABLE but got end of script at [2:10]
```

`WITH t AS` with nothing after it gives `Expected "(" but got end of script at [1:10]`, and `WITH t AS (SELECT 1 AS a),` with nothing after it gives `Expected keyword SELECT but got end of script at [1:27]`.

## The variants of the message

Four more texts, each produced by BigQuery. A `UNION` at the end of the script:

```sql
SELECT 1 UNION
```

```text
Syntax error: Expected keyword ALL or keyword DISTINCT but got end of script at [1:15]
```

A `CAST` with no closing bracket, where a `FORMAT` clause would also have been fine:

```sql
SELECT CAST(1 AS INT64
```

```text
Syntax error: Expected ")" or keyword FORMAT but got end of script at [1:23]
```

A `CASE` that continues with `FROM` where `ELSE` or `END` had to be:

```sql
SELECT CASE WHEN 1 = 1 THEN 2 FROM UNNEST([1])
```

```text
Syntax error: Expected keyword ELSE or keyword END but got keyword FROM at [1:31]
```

A `MERGE` clause that stops after `WHEN MATCHED`, where `AND` or `THEN` had to follow:

```sql
MERGE t USING u ON TRUE WHEN MATCHED
```

```text
Syntax error: Expected keyword AND or keyword THEN but got end of script at [1:37]
```

## Queries that look wrong but run

**`UNION ALL` after `UNION ALL`.** A chain of the same operator can be as long as you like:

```sql
SELECT 1 AS a
UNION ALL
SELECT 2 AS a
UNION ALL
SELECT 3 AS a
```

**A trailing comma in the select list.** `SELECT 1 AS a,` is valid.

## Different errors that look like this one

**`Syntax error: Unexpected keyword X`.** The parser did not have a list of what it wanted. See [Unexpected keyword](https://docs.querylab.io/bigquery-errors/syntax-error-unexpected-keyword/).

**`Syntax error: Unexpected end of script`.** The query stops where an expression had to be, such as `WHERE a IN (` or `JOIN` with nothing after it.

**`Syntax error: Expected end of input but got ...`.** See [Expected end of input](https://docs.querylab.io/bigquery-errors/syntax-error-expected-end-of-input/).

**`Syntax error: Expected keyword MODULE or keyword PROTO but got ...`.** The query began with `import`. See [Expected keyword MODULE or keyword PROTO](https://docs.querylab.io/bigquery-errors/expected-keyword-module-or-keyword-proto/).

## In Querylab.io

Querylab.io parses in the browser as you type and marks these before anything is sent to BigQuery. `UNION` or `INTERSECT` with no `ALL` or `DISTINCT`, `EXCEPT` with no `ALL` or `DISTINCT`, a set operation with nothing after it, an unclosed `COUNT(` and a `CASE` with no `END` get BigQuery's wording at the same token.

An unclosed subquery or `CAST`, a list like `IN (1 2)` and `GROUP` without `BY` are marked in the editor's own words, such as `Expected ) to close subquery`.

There is no quick fix for these errors. Add the missing keyword or bracket by hand.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, set operators](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)

- [Lexical structure and syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical)