Query syntax

Preview Supported BigQuery query syntax in BigQuery compatibility mode. For the |> pipe operator, see Pipe syntax.

Supported clauses

Clause	Notes
`SELECT`, `SELECT DISTINCT`	Column lists, expressions, and aliases.
`SELECT * EXCEPT (...)`	Drops the named columns from ``. `SELECT REPLACE (...)` is also read.
`FROM`	Tables, subqueries, `VALUES`, and table-valued functions.
`JOIN`	`INNER`, `LEFT`, `RIGHT`, `FULL`, and `CROSS`, with `ON`, `USING`, or `NATURAL`.
`LATERAL`	Correlated subquery and table-function joins.
`UNNEST`	As a table source, including `WITH OFFSET AS alias`. Also `expr IN UNNEST(array)`.
`WHERE`, `HAVING`	Standard predicates.
`GROUP BY`	Column lists, `GROUP BY ALL`, `GROUPING SETS`, `ROLLUP`, and `CUBE`.
`QUALIFY`	Filters on window function results.
`WINDOW`	Named window definitions.
`ORDER BY`	`ASC`/`DESC`, `NULLS FIRST`/`NULLS LAST`, and `ORDER BY ALL`.
`LIMIT ... OFFSET`	Row limit with optional offset.
`WITH`	Common table expressions, including `MATERIALIZED` and `NOT MATERIALIZED`.

Set operators UNION, INTERSECT, and EXCEPT are supported with ALL or DISTINCT.

Form	Notes
`UNION ALL` / `UNION DISTINCT`	Positional column matching.
`INTERSECT` / `EXCEPT`	With `ALL` or `DISTINCT`.
`UNION ... CORRESPONDING`	Matches columns by name. `BY NAME` is an accepted spelling.
`UNION ... STRICT CORRESPONDING`	Requires both inputs to have the same set of column names.
`UNION ... CORRESPONDING BY (col, ...)`	Matches on an explicit column list.

CORRESPONDING and BY NAME apply to UNION only. INTERSECT and EXCEPT do not accept them.
Without an explicit column list, only the FULL and STRICT forms of CORRESPONDING are supported. Subset (inner or left) matching needs an explicit BY (...) list.
WITH RECURSIVE is not supported.
PIVOT, UNPIVOT, TABLESAMPLE, and MATCH_RECOGNIZE are not supported as table sources.
UNNEST ... WITH ORDINALITY is not supported. Use WITH OFFSET AS alias.
ORDER BY does not support WITH FILL or INTERPOLATE.
DISTINCT ON, LIMIT ... BY, FETCH, and row-locking clauses are not supported.
A table-valued function call must be unqualified. A schema-qualified call (schema.func(...)) is not supported.