SELECT | Supported | Including DISTINCT and SELECT * EXCEPT (...). |
FROM | Supported | Tables, views, subqueries, VALUES, and the table-valued functions. |
Queries that start with FROM | Databricks mode only | FROM t SELECT a. |
JOIN | Supported | INNER, LEFT, RIGHT, FULL, and CROSS, with ON, USING, or NATURAL; LEFT SEMI JOIN and LEFT ANTI JOIN. |
WHERE | Supported | Scalar, IN, and EXISTS subqueries. |
GROUP BY | Supported | Including ALL, ordinals, GROUPING SETS, ROLLUP, CUBE, and WITH ROLLUP, and FILTER (WHERE ...) on an aggregate. |
HAVING | Supported | |
QUALIFY | Supported | Databricks syntax, accepted in both modes. Open-source Spark rejects it. |
WINDOW | Supported | Named windows, explicit frames, and WITHIN GROUP (ORDER BY ...) on percentile_cont, listagg, and string_agg. |
ORDER BY | Supported | ASC/DESC and NULLS FIRST/NULLS LAST. Without either, NULLs sort first ascending and last descending, as in Spark. |
SORT BY, CLUSTER BY, DISTRIBUTE BY | 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, OFFSET | Supported | |
WITH | Supported | Including the Spark form without AS, WITH q (SELECT ...). WITH RECURSIVE is not supported. |
TABLE t | Supported | Reads as SELECT * FROM t. |
VALUES | Supported | As a query and as a table with an alias and column list, VALUES (1, 'a') AS v(x, y). |
|> pipe syntax | Supported | Including WHERE, SELECT, EXTEND, SET, AS, AGGREGATE, JOIN, ORDER BY, and LIMIT. |
| Hints | Accepted and ignored | /*+ BROADCAST(t) */ and other hints parse and do not change the plan. |
IDENTIFIER() | Supported | A column in the select list or a table in FROM, from a constant string. |
PIVOT, UNPIVOT | Not supported | |
LATERAL VIEW, LATERAL subqueries and table functions | Not supported | |
TABLESAMPLE | Not supported | Rejected rather than ignored, because ignoring it would return the whole table. |
VERSION AS OF, TIMESTAMP AS OF | Not supported | Time travel. Databricks mode rejects it with an explanatory error; SparkSQL mode does not parse it. |
