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 operations

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.

Query parameters

BigQuery named query parameters are written as @name. Bind values with the query_parameters request property; see Parametrized queries for passing parameter values from different client SDKs, and the SQL guide for parameter value types.

SELECT id FROM orders WHERE category = @category AND total > @min_total;

A @name reference is case-insensitive. Referencing a parameter that was not set raises an error.

Limitations

Query parameters support the named @name form only. Positional (?) parameters are not supported.
@@name system variables are not supported.
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.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Supported clauses

Set operations

Query parameters

Limitations

​Supported clauses

​Set operations

​Query parameters

​Limitations

Supported clauses

Set operations

Query parameters

Limitations