Syntax error: WHERE not supported after FROM query

A query that starts with FROM is a pipe query, and a bare WHERE cannot follow it. BigQuery tells you to use |> WHERE. The exact clause it suggests differs per clause, and GROUP BY gets a surprising one.

Syntax error: WHERE not supported after FROM query; Consider using pipe operator `|> WHERE` at [1:22]

What BigQuery means

You wrote a query that begins with FROM. That is legal on its own, and it is the start of a pipe query. But once a query starts with FROM, the only thing that can follow the table is a pipe operator. A bare WHERE after it is not part of either grammar, so BigQuery stops and suggests the pipe form.

Pipe syntax is native BigQuery SQL, so the fix below runs in the console, the bq CLI and anywhere else. Nothing here is an extension.

The smallest query that reproduces it

FROM (SELECT 1 AS x) WHERE x = 1

The position, 22, is the WHERE.

The fix

Add the pipe operator:

FROM (SELECT 1 AS x)
|> WHERE x = 1

Or write it as an ordinary query:

SELECT * FROM (SELECT 1 AS x) WHERE x = 1

Both are valid. The pipe form is worth keeping if you are going to add more steps, because each step sees the output of the one before it.

How you get here by accident

Two ways, mostly.

You deleted the SELECT line while editing and left the rest. FROM orders WHERE status = 'paid' looks like a complete query and is not.

Or you started writing a pipe query, got the first step right, and then wrote the second one without the |>:

FROM orders
|> WHERE status = 'paid'
GROUP BY customer_id

The GROUP BY has no pipe in front of it. This one gets no hint, only Syntax error: Expected end of input but got keyword GROUP at [3:1], because by then the parser is past the FROM query and inside the pipe chain.

The message per clause

BigQuery words the hint differently depending on which clause it found. These are the ones that get a suggestion:

WHERE not supported after FROM query; Consider using pipe operator `|> WHERE`
SELECT not supported after FROM query; Consider using pipe operator `|> SELECT`
GROUP BY not supported after FROM query; Consider using pipe operator `|> AGGREGATE`
ORDER BY not supported after FROM query; Consider using pipe operator `|> ORDER BY` or parentheses around the FROM query
LIMIT not supported after FROM query; Consider using pipe operator `|> LIMIT` or parentheses around the FROM query

The GROUP BY line is the one to read twice. It suggests |> AGGREGATE rather than a pipe named after the clause you wrote, because in pipe syntax grouping and aggregation are one step:

FROM orders
|> AGGREGATE SUM(total) AS revenue GROUP BY customer_id

HAVING, QUALIFY and WINDOW get no hint. They fall back to the generic message, for example Syntax error: Expected end of input but got keyword HAVING at [1:22].

There is no |> HAVING and no |> QUALIFY either. Both give Syntax error: Expected keyword JOIN but got keyword HAVING. Pipe syntax does not need them, because each step filters whatever the previous step produced. A |> WHERE after |> AGGREGATE can refer to the aggregate’s alias, and a |> WHERE after |> WINDOW can refer to the window function’s alias:

FROM orders
|> AGGREGATE SUM(total) AS revenue GROUP BY customer_id
|> WHERE revenue > 1000

What does work after a FROM query

A JOIN is fine without a pipe, because the join is part of the FROM itself:

FROM (SELECT 1 AS x) JOIN (SELECT 1 AS y) ON x = y

A FROM query with nothing after it is also fine. FROM orders on its own returns every column of every row.

And the two styles mix. You can pipe off the end of a normal query:

SELECT * FROM (SELECT 1 AS x) |> WHERE x = 1

In Querylab.io

Querylab.io marks this in the editor as you type, before anything is sent to BigQuery. See Pipe syntax for what the editor does with pipe queries, and Pipe syntax in BigQuery for the clause list.