SELECT (SQL Analytics)
Retrieves result sets from one or more tables.
Syntax
[ WITH with_query [ , ... ] ]
select_statement [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_statement, ... ]
[ ORDER BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ]
[ SORT BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ]
[ CLUSTER BY { expression [ , ... ] } ]
[ DISTRIBUTE BY { expression [, ... ] } ]
[ WINDOW { named_window [ , WINDOW named_window, ... ] } ]
[ LIMIT { ALL | expression } ]
While select_statement
is defined as
SELECT [ hints , ... ] [ ALL | DISTINCT ] { named_expression [ , ... ] }
FROM { from_item [ , ... ] }
[ PIVOT clause ]
[ LATERAL VIEW clause ] [ ... ]
[ WHERE boolean_expression ]
[ GROUP BY expression [ , ... ] ]
[ HAVING boolean_expression ]
Parameters
with_query
One or more common table expressions before the main query block.These table expressions can be referenced later in the
FROM
clause. This is useful to abstract out repeated subquery blocks in theFROM
clause and improves readability of the query.-
Hints help the SQL Analytics optimizer make better planning decisions. SQL Analytics supports hints that influence selection of join strategies and repartitioning of the data.
ALL
Select all matching rows from the relation. Enabled by default.
DISTINCT
Select all matching rows from the relation after removing duplicates in results.
named_expression
An expression with an assigned name. Denotes a column expression.
Syntax:
expression [AS] [alias]
from_item
A source of input for the query. One of the following:
Table identifier
[database_name.] table_name
: A table name, optionally qualified with a database name.delta.`<path-to-table>`
: The location of an existing Delta table. You can specify a time travel version usingTIMESTAMP AS OF
,VERSION AS OF
, or@
syntax after a table relation. See Query an older snapshot of a table (time travel) for details.
- JOIN
- Table-valued function
- Inline table
- Subquery
-
Used for data perspective; you can get the aggregated values based on specific column value.
-
Used in conjunction with generator functions such as
EXPLODE
, which generates a virtual table containing one or more rows.LATERAL VIEW
applies the rows to each original output row. WHERE
Filters the result of the
FROM
clause based on the supplied predicates.-
The expressions that are used to group the rows. This is used in conjunction with aggregate functions (
MIN
,MAX
,COUNT
,SUM
,AVG
) to group rows based on the grouping expressions and aggregate values in each group. When aFILTER
clause is attached to an aggregate function, only the matching rows are passed to that function. -
The predicates by which the rows produced by
GROUP BY
are filtered. TheHAVING
clause is used to filter rows after the grouping is performed. If you specifyHAVING
withoutGROUP BY
, it indicates aGROUP BY
without grouping expressions (global aggregate). -
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. -
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. -
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. -
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. boolean_expression
Any expression that evaluates to a result type
Boolean
. You can combine two or more expressions together using the logical operators (AND
,OR
).expression
A combination of one or more values, operators, and SQL functions that evaluates to a value.
named_window
Aliases for one or more source window specifications. The source window specifications can be referenced in the window definitions in the query.