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

> Spark SQL and Databricks SQL query syntax supported in SparkSQL and Databricks compatibility modes.

Preview

Spark [query syntax](https://spark.apache.org/docs/latest/sql-ref-syntax.html#data-retrieval-statements) in [SparkSQL and Databricks compatibility modes](https://docs.firebolt.io/reference-sql/spark/overview.md). Both modes accept everything listed here unless a row names one mode.

## Supported clauses

| Clause | Support | Notes |
| :- | :- | :- |
| [`SELECT`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select.html) | Supported | Including `DISTINCT` and `SELECT * EXCEPT (...)`. |
| [`FROM`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select.html) | Supported | Tables, views, subqueries, `VALUES`, and the [table-valued functions](https://docs.firebolt.io/reference-sql/spark/functions/table-valued.md). |
| Queries that start with `FROM` | Databricks mode only | `FROM t SELECT a`. |
| [`JOIN`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-join.html) | Supported | `INNER`, `LEFT`, `RIGHT`, `FULL`, and `CROSS`, with `ON`, `USING`, or `NATURAL`; `LEFT SEMI JOIN` and `LEFT ANTI JOIN`. |
| [`WHERE`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-where.html) | Supported | Scalar, `IN`, and `EXISTS` subqueries. |
| [`GROUP BY`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-groupby.html) | Supported | Including `ALL`, ordinals, `GROUPING SETS`, `ROLLUP`, `CUBE`, and `WITH ROLLUP`, and `FILTER (WHERE ...)` on an aggregate. |
| [`HAVING`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-having.html) | Supported | |
| [`QUALIFY`](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-qualify) | Supported | Databricks syntax, accepted in both modes. Open-source Spark rejects it. |
| [`WINDOW`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-window.html) | Supported | Named windows, explicit frames, and `WITHIN GROUP (ORDER BY ...)` on `percentile_cont`, `listagg`, and `string_agg`. |
| [`ORDER BY`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-orderby.html) | Supported | `ASC`/`DESC` and `NULLS FIRST`/`NULLS LAST`. Without either, NULLs sort first ascending and last descending, as in Spark. |
| [`SORT BY`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-sortby.html), [`CLUSTER BY`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-clusterby.html), [`DISTRIBUTE BY`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-distribute-by.html) | Supported | `SORT BY` and `CLUSTER BY` sort the whole result, which satisfies Spark's per-partition order. `DISTRIBUTE BY` alone has no effect on the result. |
| [`LIMIT`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-limit.html), [`OFFSET`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-offset.html) | Supported | |
| [`WITH`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-cte.html) | Supported | Including the Spark form without `AS`, `WITH q (SELECT ...)`. `WITH RECURSIVE` is not supported. |
| [`TABLE t`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select.html) | Supported | Reads as `SELECT * FROM t`. |
| [`VALUES`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-inline-table.html) | Supported | As a query and as a table with an alias and column list, `VALUES (1, 'a') AS v(x, y)`. |
| [`\|>` pipe syntax](https://spark.apache.org/docs/latest/sql-pipe-syntax.html) | Supported | Including `WHERE`, `SELECT`, `EXTEND`, `SET`, `AS`, `AGGREGATE`, `JOIN`, `ORDER BY`, and `LIMIT`. |
| [Hints](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html) | Accepted and ignored | `/*+ BROADCAST(t) */` and other hints parse and do not change the plan. |
| [`IDENTIFIER()`](https://spark.apache.org/docs/latest/sql-ref-identifier-clause.html) | Supported | A column in the select list or a table in `FROM`, from a constant string. |
| [`PIVOT`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-pivot.html), [`UNPIVOT`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-unpivot.html) | Not supported | |
| [`LATERAL VIEW`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-lateral-view.html), `LATERAL` subqueries and table functions | Not supported | |
| [`TABLESAMPLE`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-sampling.html) | Not supported | Rejected rather than ignored, because ignoring it would return the whole table. |
| [`VERSION AS OF`, `TIMESTAMP AS OF`](https://docs.databricks.com/aws/en/delta/history) | Not supported | Time travel. Databricks mode rejects it with an explanatory error; SparkSQL mode does not parse it. |

## Set operations

| Operation | Support | Notes |
| :- | :- | :- |
| [`UNION`, `UNION ALL`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-setops.html) | Supported | Including `BY NAME`. |
| [`INTERSECT`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-setops.html) | Supported | |
| [`EXCEPT`, `MINUS`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-setops.html) | Supported | `MINUS` is a synonym for `EXCEPT`. |

## Literals and identifiers

| Construct | Support | Notes |
| :- | :- | :- |
| Single- and double-quoted strings | Supported | A double-quoted token is a string, as in Spark: `SELECT "a"` returns `a`. |
| Backslash escapes, such as `'a\tb'` | Databricks mode only | SparkSQL mode keeps a backslash as an ordinary character. See [Choosing a mode](https://docs.firebolt.io/reference-sql/spark/overview.md#choosing-a-mode). |
| Raw strings, `r'...'` | Not supported | |
| Adjacent string literals, `'a' 'b'` | Not supported | Use `\|\|` or `concat`. |
| Typed literals: `DATE'...'`, `TIMESTAMP'...'`, `TIMESTAMP_NTZ'...'`, `X'...'`, `INTERVAL ...` | Supported | Including multi-unit and qualified intervals, such as `INTERVAL '1-2' YEAR TO MONTH` and `INTERVAL 2 HOURS`. |
| Typed numeric literals: `1Y`, `1S`, `1L`, `1.5F`, `1.5D`, `1.5BD` | SparkSQL mode only | `1Y` and `1S` become `INTEGER`, since Firebolt has no narrower integer type. |
| Backtick-quoted identifiers | Supported | |
| Comments | Supported | `--` line comments and nested `/* */` block comments. |

## Limitations

* Identifiers are returned in lower case. Spark keeps the case an alias is written in, so a column selected as `Foo` comes back as `Foo` there and `foo` here. A client that looks up result columns by name needs to be case-insensitive.
* String comparison is case-sensitive, as in Spark's default collation. Collations, such as `COLLATE UTF8_LCASE`, are not supported.
* A three-part name reads its first part as a Firebolt database: `db.schema.t` works, and `spark_catalog.default.t` fails because no database is named `spark_catalog`. Two-part `schema.table` names work.
* Query parameters, `:name` and `?`, are not supported.
* An unaliased expression column is named `?column?` where Spark names it after the expression's text. Function calls and literals are named as in Spark. Alias any column a client reads by name.


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