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

> 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) | Supported | Snowflake's synonym for `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`.


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