Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Retrieves result sets from one or more tables.
Applies to: Databricks SQL Databricks Runtime
[ 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 ]}
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
, or INTERSECT
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
, and DISTRIBUTE 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
and CLUSTER 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
and CLUSTER 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
and SORT 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,
and ORDER BY
to produce a deterministic result.
Note
When paging through a result set using LIMIT
and OFFSET
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
.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Write multi-table queries by using Kusto Query Language - Training
Learn how to write Kusto Query Language (KQL) queries to combine and retrieve data from two or more tables by using the `lookup`, `join`, and `union` operators.