SELECT
Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API
Retrieves data from a table or view.
SELECT
query syntax uses the required SELECT
statement with one or more optional clauses.
with_query
is defined as:
from_item
is one of:
grouping_element
is defined as:
sample_size
is one of:
This set of rules describes how the clauses defined in the SELECT
statement are processed.
-
The queries in the
WITH
list are temporary tables that are referenced in theFROM
list. AWITH
is computed only one time. -
All elements in the
FROM
list are computed. If more than one element is specified in theFROM
list, they’re cross-joined. -
If the
WHERE
clause is specified, all rows that don’t satisfy the condition are removed from the output. -
If there’s a
GROUP BY
clause or aggregate function calls, the output is combined into groups and the results of aggregate functions are computed. If theHAVING
clause is present, it eliminates groups that don’t satisfy the given condition. -
The output rows are computed by using the
SELECT
output expressions for each selected row or row group. -
SELECT DISTINCT
removes duplicate rows from the result.SELECT DISTINCT ON
removes rows that match the specified expressions.SELECT ALL
(the default) returns candidate rows, including duplicates. -
By using the operators
UNION
,INTERSECT
, andEXCEPT
, the output of more than oneSELECT
statement can be combined to form a single result set. -
If the
ORDER BY
clause is specified, the returned rows are sorted in the specified order. -
If the
LIMIT
(orFETCH FIRST
) orOFFSET
clause is specified, theSELECT
statement only returns a subset of the result rows.
WITH
ClauseThe
WITH
clause allows you to specify one or more subqueries that can be referenced by name in the primary query. See WITH Clause.FROM
ClauseThe
FROM
clause specifies one or more source tables for theSELECT
. See FROM Clause.WHERE
ClauseThe optional
WHERE
clause is any expression that evaluates to a result of typeboolean
. Any row that doesn’t satisfy this condition is eliminated from the output.GROUP BY
ClauseGROUP BY
combines all selected rows that share values for the grouped expressions into one row. See GROUP BY Clause.HAVING
ClauseHAVING
removes group rows that don’t satisfy the condition. Each<condition>
reference a grouping column, unless the reference appears in an aggregate function.HAVING
turns a query into a grouped query even if there’s noGROUP BY
clause. TheSELECT
list andHAVING
clause can only reference table columns from aggregate functions. A row is emitted if theHAVING
condition is true and zero rows for false.WINDOW
ClauseThe
WINDOW
clause returns a partition of the data returned by the command. See WINDOW Clause.SELECT
ListThe
SELECT
list defines expressions that form the output rows of theSELECT
statement. The expressions can refer to columns computed in theFROM
clause. See SELECT Clause.DISTINCT
ClauseSELECT DISTINCT
removes all duplicate rows from the result set. See DISTINCT Clause.UNION
,INTERSECT
, andEXCEPT
OperatorsThe
UNION
operator computes the set union of the rows returned by the involvedSELECT
statements.The
INTERSECT
operator computes the set intersection of the rows returned by the involvedSELECT
statements.The
EXCEPT
operator computes the set of rows that are in the result of the leftSELECT
statement but not in the result of the right one.ORDER BY
ClauseThe
ORDER BY
clause sorts the result rows by the specified expressions. See ORDER BY Clause.LIMIT
,FETCH
, andOFFSET
ClausesThe
LIMIT
clause specifies the maximum number of rows to return.OFFSET
specifies the number of rows to skip before starting to return rows. See LIMIT and OFFEST Clauses.
This example joins the table films
with the table distributors
.
You can then sum the column len
of all films and group the results by kind
.
This example sums the column len
of all films, groups the results by kind
, and shows the group totals that are less than 5 hours.
The next two examples are two ways of sorting the individual results according to the contents of the second column (name
).
The next example shows how to obtain the union of the tables distributors
and actors
, limiting the results to names that begin with the letter 'W' in each table. Only distinct rows are wanted, so the key word ALL
is omitted.
This example shows how to use a function in the FROM
clause.
This example shows how to use a simple WITH
clause. In this example, the WITH
query evaluates only one time so that we get two sets of the same three random values.
This example uses WITH RECURSIVE
to find all direct or indirect subordinates
of employee Mary, and their level of indirectness, from a
table that shows only direct subordinates.
This example shows the use of TABLESAMPLE
to query a table with a sequence of 100 integers. It uses the sampling method SYSTEM
to return 10% of the table’s rows but is bounded to a maximum of 6 rows.
Because the table has 100 rows and the percentage yields 10 rows, the result is truncated to 6 rows. Using the REPEATABLE
clause with the same argument returns the same sample every time the command is executed, if the table isn’t modified between queries.
This example shows the use of GROUPING SETS
. To group multiple sets of columns within a single query, see Grouping Sets.
The SELECT
statement is compatible with the SQL standard but excludes some features and extends some functionality.
You can omit the FROM
clause in Data Cloud SQL. For example, you can compute the results of simple expressions.
If you don’t specify a FROM
clause, the query can’t reference any database tables. For example, this query is invalid.
The list of output expressions after SELECT
can be empty, producing a zero-column result table. However, you can use an empty list with DISTINCT
.
AS
is required if the new column name matches any keyword. To prevent conflicts with keyword additions, use AS
or double-quote output column names.
In FROM
items, you can omit AS
before an unreserved keyword alias.
You can only use the TABLESAMPLE
clause on regular tables.
With Data Cloud SQL you can write a function call as a member of the
FROM
list.
Data Cloud SQL supports ORDER BY and GROUP BY to specify expressions as input-column names.
The clauses LIMIT
and OFFSET
are specific to Data Cloud SQL.
DISTINCT ON ( ... )
is an extension of the SQL standard.