> ## Documentation Index
> Fetch the complete documentation index at: https://firebolt-aggregate-helm-docs-pr-97.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Query syntax

> Snowflake query syntax supported in Snowflake compatibility mode.

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

Snowflake [query syntax](https://docs.snowflake.com/en/sql-reference/constructs) in [Snowflake compatibility mode](/reference-sql/snowflake/overview).

## Supported clauses

| Clause                                                                                                                                               | Support       | Notes                                                                      |
| :--------------------------------------------------------------------------------------------------------------------------------------------------- | :------------ | :------------------------------------------------------------------------- |
| [`SELECT`](https://docs.snowflake.com/en/sql-reference/sql/select)                                                                                   | Supported     | Including `DISTINCT` and `SELECT * EXCLUDE`.                               |
| [`FROM`](https://docs.snowflake.com/en/sql-reference/constructs/from)                                                                                | Supported     | Tables, views, subqueries, and `LATERAL` subqueries.                       |
| [`JOIN`](https://docs.snowflake.com/en/sql-reference/constructs/join)                                                                                | Supported     | Inner and outer joins, `CROSS JOIN`, `NATURAL JOIN`, and `JOIN ... USING`. |
| [`WHERE`](https://docs.snowflake.com/en/sql-reference/constructs/where)                                                                              | Supported     | Scalar and `EXISTS` subqueries.                                            |
| [`GROUP BY`](https://docs.snowflake.com/en/sql-reference/constructs/group-by)                                                                        | Supported     | Including `ALL`, ordinals, `GROUPING SETS`, `ROLLUP`, and `CUBE`.          |
| [`HAVING`](https://docs.snowflake.com/en/sql-reference/constructs/having)                                                                            | Supported     |                                                                            |
| [`QUALIFY`](https://docs.snowflake.com/en/sql-reference/constructs/qualify)                                                                          | Supported     | Filters on window function results.                                        |
| [`ORDER BY`](https://docs.snowflake.com/en/sql-reference/constructs/order-by)                                                                        | Supported     | Including `NULLS FIRST` and `NULLS LAST`.                                  |
| [`LIMIT`](https://docs.snowflake.com/en/sql-reference/constructs/limit)                                                                              | Supported     | With `OFFSET`.                                                             |
| [`FETCH`](https://docs.snowflake.com/en/sql-reference/constructs/limit)                                                                              | Not supported | `FETCH FIRST ... ROWS ONLY` is rejected. Use `LIMIT` with `OFFSET`.        |
| [`WITH`](https://docs.snowflake.com/en/sql-reference/constructs/with)                                                                                | Supported     | `WITH RECURSIVE` is not supported.                                         |
| [Window functions](https://docs.snowflake.com/en/sql-reference/functions-window)                                                                     | Supported     | `PARTITION BY`, explicit frames, and `IGNORE NULLS`.                       |
| `LISTAGG ... WITHIN GROUP`                                                                                                                           | Supported     |                                                                            |
| [`TOP n`](https://docs.snowflake.com/en/sql-reference/constructs/top_n)                                                                              | Not supported | Use `LIMIT`.                                                               |
| [`PIVOT`](https://docs.snowflake.com/en/sql-reference/constructs/pivot), [`UNPIVOT`](https://docs.snowflake.com/en/sql-reference/constructs/unpivot) | Not supported |                                                                            |
| [`MATCH_RECOGNIZE`](https://docs.snowflake.com/en/sql-reference/constructs/match_recognize)                                                          | Not supported |                                                                            |
| [`CONNECT BY`](https://docs.snowflake.com/en/sql-reference/constructs/connect-by)                                                                    | Not supported |                                                                            |
| `ASOF JOIN`                                                                                                                                          | Not supported |                                                                            |
| [`AT` / `BEFORE` / `CHANGES`](https://docs.snowflake.com/en/sql-reference/constructs/at-before)                                                      | Not supported | Time travel.                                                               |
| [`FLATTEN`](https://docs.snowflake.com/en/sql-reference/functions/flatten)                                                                           | Not supported | `LATERAL FLATTEN` and `TABLE(FLATTEN(...))`.                               |
| [`IDENTIFIER()`](https://docs.snowflake.com/en/sql-reference/identifier-literal)                                                                     | Not supported |                                                                            |
| [`SAMPLE` / `TABLESAMPLE`](https://docs.snowflake.com/en/sql-reference/constructs/sample)                                                            | Not supported | Row sampling is not available. Use `WHERE RANDOM() < 0.1`.                 |
| `SELECT * ILIKE`                                                                                                                                     | Not supported |                                                                            |

## Set operations

| Operation                                                                           | Support       | Notes                                                             |
| :---------------------------------------------------------------------------------- | :------------ | :---------------------------------------------------------------- |
| [`UNION`, `UNION ALL`](https://docs.snowflake.com/en/sql-reference/operators-query) | Supported     |                                                                   |
| [`INTERSECT`](https://docs.snowflake.com/en/sql-reference/operators-query)          | Supported     |                                                                   |
| [`EXCEPT`](https://docs.snowflake.com/en/sql-reference/operators-query)             | Supported     |                                                                   |
| [`MINUS`](https://docs.snowflake.com/en/sql-reference/operators-query)              | Not supported | Snowflake's synonym for `EXCEPT` is not accepted. Write `EXCEPT`. |

## Query parameters

Snowflake bind variables are written as `:name` (named) or `:N` (positional). 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. `:N` uses the same positional convention as `$N` (so `:1` is bound as `$1`), which keeps positional binding uniform across dialects:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT id FROM orders WHERE category = :category AND priority = :1;
```

The infix colon (`value:field`) is the [semi-structured accessor](/reference-sql/snowflake/operators), not a parameter. Referencing a parameter that was not set raises an error.

```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
Snowflake bind variables (dialect=snowflake):
- Write :name (named) or :N (positional); bind values via the query_parameters
  request property. :N is positional and shares the $N convention (:1 -> JSON
  name "$1"), so positional binding is identical across dialects; :name -> "name".
- Value JSON type sets the type: integer, number, boolean, string are native
  (no CAST); null is SQL NULL. DATE, TIMESTAMP, NUMBER, BINARY: bind a string
  and CAST. No array/struct-typed parameter.
- An unset bind errors: "Query referenced parameter '<name>', but it was not set".
- The infix expr:field is the semi-structured accessor, not a parameter.
- `?` positional, `$name` session variables, and `@name` stage references are
  not supported as parameters and error with a message pointing to :name / :N.
```

## Limitations

* Query parameters use the `:name` / `:N` bind form only. `?` positional parameters, `$name` session variables, and `@name` stage references are not supported.
* Fully qualified `database.schema.table` names are not supported and fail with `Database 'mydb' does not exist`. Two-part `schema.table` names work. Most tool-generated Snowflake SQL is fully qualified, so this is usually the first thing a migrated workload hits.
* Identifiers are returned in lower case. Snowflake folds unquoted identifiers to upper case, so a column selected as `n` comes back as `N` there and `n` here. A client that looks up result columns by name needs to be case-insensitive.
* `$$`-quoted string literals are not supported.
* `FETCH FIRST ... ROWS ONLY` is not supported; use `LIMIT` with `OFFSET`.
* `MINUS` is not accepted as a synonym for `EXCEPT`.