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 ]

subquery
{ SELECT clause |
  VALUES clause |
  ( query ) |
  TABLE [ table_name | view_name ]}

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.

    • SELECT

      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 Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

  • 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.2 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.