> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Query syntax

> BigQuery GoogleSQL query syntax supported in Firebolt's BigQuery compatibility mode.

<span className="feature-tag">Preview</span>

Supported BigQuery [query syntax](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax) in [BigQuery compatibility mode](/reference-sql/bigquery/overview). For the `|>` pipe operator, see [Pipe syntax](/reference-sql/bigquery/pipe-syntax).

## Supported clauses

| Clause | Notes |
| :- | :- |
| [`SELECT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_list), [`SELECT DISTINCT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_distinct) | Column lists, expressions, and aliases. |
| [`SELECT * EXCEPT (...)`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except) | Drops the named columns from `*`. `SELECT * REPLACE (...)` is also read. |
| [`FROM`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#from_clause) | Tables, subqueries, `VALUES`, and table-valued functions. |
| [`JOIN`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#join_operation) | `INNER`, `LEFT`, `RIGHT`, `FULL`, and `CROSS`, with `ON`, `USING`, or `NATURAL`. |
| [`LATERAL`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#correlated_join) | Correlated subquery and table-function joins. |
| [`UNNEST`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator) | As a table source, including `WITH OFFSET AS alias`. Also `expr IN UNNEST(array)`. |
| [`WHERE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#where_clause), [`HAVING`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#having_clause) | Standard predicates. |
| [`GROUP BY`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_clause) | Column lists, [`GROUP BY ALL`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_all), [`GROUPING SETS`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_grouping_sets), [`ROLLUP`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_rollup), and [`CUBE`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_cube). |
| [`QUALIFY`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause) | Filters on window function results. |
| [`WINDOW`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#window_clause) | Named window definitions. |
| [`ORDER BY`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause) | `ASC`/`DESC`, `NULLS FIRST`/`NULLS LAST`, and `ORDER BY ALL`. |
| [`LIMIT ... OFFSET`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#limit_and_offset_clause) | Row limit with optional offset. |
| [`WITH`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause) | Common table expressions, including `MATERIALIZED` and `NOT MATERIALIZED`. |

## Set operations

[Set operators](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set_operators) `UNION`, `INTERSECT`, and `EXCEPT` are supported with `ALL` or `DISTINCT`.

| Form | Notes |
| :- | :- |
| [`UNION ALL` / `UNION DISTINCT`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#union) | Positional column matching. |
| `INTERSECT` / `EXCEPT` | With `ALL` or `DISTINCT`. |
| [`UNION ... CORRESPONDING`](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#by_name_or_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](/guides/developing-with-firebolt/parametrized-queries) for passing parameter values from different client SDKs, and the [SQL guide](/guides/sql-dialect/parametrized-queries) for parameter value types.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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.

```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
Named parameters (dialect=bigquery):
- Write @name and bind values via the query_parameters request property; see
  the parametrized-queries guides for how to pass and type them. @name is
  case-insensitive and matches a lowercase name.
- INT64, FLOAT64, BOOL, STRING, and NULL are native (no cast). DATE, DATETIME,
  TIMESTAMP, NUMERIC, BYTES, and JSON need an explicit CAST. There is no array-
  or struct-typed parameter; pass a delimited string and SPLIT, or a JSON string
  and cast to JSON.
- An unset parameter errors: "Query referenced parameter @<name>, but it was
  not set". Positional (?) parameters and @@name system variables are not
  supported.
```

## 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.