Share via

Query

Retrieves result sets from one or more tables.

Applies to: check marked yes Databricks SQL check marked yes 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 ]
  [ { |> piped_operation } [ ...] ]

subquery
{ subselect |
  VALUES clause |
  ( query ) |
  TABLE [ table_name | view_name ] |
  FROM table_reference [, ...] }

Parameters

  • common table expression

    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.

    • subselect

      A subquery consisting of a SELECT FROM WHERE pattern.

    • VALUES

      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.

      • table_name

        Identifies the table to be returned.

      • view_name

        Identifies the view to be returned.

      If the table or view cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

    • FROM

      Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.2 and above

      Returns the cross join of all table-references. This clause is typically used as the root of a SQL pipeline.

      • table_reference

        A source of input for the SELECT. This input reference can be turned into a streaming reference by using the STREAM keyword prior to the reference.

    A source of input for the SELECT. This input reference can be turned into a streaming reference by using the STREAM keyword prior to the reference.

  • set_operator

    A construct combining subqueries using UNION, EXCEPT, or INTERSECT operators.

  • ORDER BY

    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.

  • DISTRIBUTE BY

    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.

  • SORT BY

    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.

  • CLUSTER BY

    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.

  • LIMIT

    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.

  • OFFSET

    Applies to: check marked yes Databricks SQL check marked yes 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.

  • WINDOW

    Defines named window specifications that can be shared by multiple Window functions in the select_query.

  • |> piped_operation

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.2 and above

    Specifies a pipelined operation to be performed on the result set of the subquery and the other preceding clauses.