Query
Retrieves result sets from one or more tables.
Applies to: Databricks SQL Databricks Runtime
Syntax
[ common_table_expression ]
{ subquery | set_operator }
[ ORDER BY clause | { [ DISTRIBUTE BY clause ] [ SORT BY clause ] } | CLUSTER BY clause ]
[ WINDOW clause ]
[ LIMIT clause ]
[ OFFSET clause ]
subquery
{ SELECT clause |
VALUES clause |
( query ) |
TABLE [ table_name | view_name ]}
Parameters
-
Common table expressions (CTE) are one or more named queries which can be reused multiple times within the main query block to avoid repeated computations or to improve readability of complex, nested queries.
subquery
One of several constructs producing an intermediate result set.
-
A subquery consisting of a
SELECT FROM WHERE
pattern. -
Specified an inline temporary table.
( query )
A nested invocation of a query which may contain set operators or common table expressions.
TABLE
Returns the entire table or view.
-
Identifies the table to be returned.
-
Identifies the view to be returned.
If the table or view cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
-
-
-
A construct combining subqueries using
UNION
,EXCEPT
, orINTERSECT
operators. -
An ordering of the rows of the complete result set of the query. The output rows are ordered across the partitions. This parameter is mutually exclusive with
SORT BY
,CLUSTER BY
, andDISTRIBUTE BY
and cannot be specified together. -
A set of expressions by which the result rows are repartitioned. This parameter is mutually exclusive with
ORDER BY
andCLUSTER BY
and cannot be specified together. -
An ordering by which the rows are ordered within each partition. This parameter is mutually exclusive with
ORDER BY
andCLUSTER BY
and cannot be specified together. -
A set of expressions that is used to repartition and sort the rows. Using this clause has the same effect of using
DISTRIBUTE BY
andSORT BY
together. -
The maximum number of rows that can be returned by a statement or subquery. This clause is mostly used in the conjunction with
ORDER BY
to produce a deterministic result. -
Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above
Skips a number of rows returned by a statement or subquery. This clause is mostly used in the conjunction with
LIMIT
to page through a result set, andORDER BY
to produce a deterministic result.Note
When paging through a result set using
LIMIT
andOFFSET
the skipped rows still get processed. These rows merely get suppressed from the result set. Pagination with this technique is not advised for resource-intensive queries. -
Defines named window specifications that can be shared by multiple Window functions in the
select_query
.
Related articles
- CLUSTER BY clause
- Common table expression (CTE)
- DISTRIBUTE BY clause
- GROUP BY clause
- HAVING clause
- Hints
- VALUES clause
- JOIN
- LATERAL VIEW clause
- LIMIT clause
- ORDER BY clause
- PIVOT clause
- Set operator
- SORT BY clause
- Table reference
- TABLESAMPLE clause
- Table-valued function (TVF)
- UNPIVOT clause
- WATERMARK clause
- WHERE clause
- WINDOW clause
- Window functions