Query (Databricks SQL)

Retrieves result sets from one or more tables.

Syntax

[ common_table_expression ]
  { subquery | set_operator }
  [ ORDER BY clause | { [ DISTRIBUTE BY clause ] [ SORT BY clause ] } | CLUSTER BY clause ]
  [ WINDOW clause ]
  [ LIMIT 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 teh view to be returned.

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

  • WINDOW

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