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.

  1. ‌ The queries in the WITH list are temporary tables that are referenced in the FROM list. A WITH is computed only one time.

  2. All elements in the FROM list are computed. If more than one element is specified in the FROM list, they’re cross-joined.

  3. If the WHERE clause is specified, all rows that don’t satisfy the condition are removed from the output.

  4. 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 the HAVING clause is present, it eliminates groups that don’t satisfy the given condition.

  5. The output rows are computed by using the SELECT output expressions for each selected row or row group.

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

  7. By using the operators UNION, INTERSECT, and EXCEPT, the output of more than one SELECT statement can be combined to form a single result set.

  8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order.

  9. If the LIMIT (or FETCH FIRST) or OFFSET clause is specified, the SELECT statement only returns a subset of the result rows.

WITH Clause

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the primary query. See WITH Clause.

FROM Clause

The FROM clause specifies one or more source tables for the SELECT. See FROM Clause.

WHERE Clause

The optional WHERE clause is any expression that evaluates to a result of type boolean. Any row that doesn’t satisfy this condition is eliminated from the output.

GROUP BY Clause

GROUP BY combines all selected rows that share values for the grouped expressions into one row. See GROUP BY Clause.

HAVING Clause

HAVING 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 no GROUP BY clause. The SELECT list and HAVING clause can only reference table columns from ‌aggregate functions. A row is emitted if the HAVING condition is true and zero rows for false.

WINDOW Clause

The WINDOW clause returns a partition of the data returned by the command. See WINDOW Clause.

SELECT List

The SELECT list defines expressions that form the output rows of the SELECT statement. The expressions can refer to columns computed in the FROM clause. Using SELECT DISTINCT, output rows can be deduplicated. See SELECT Clause and DISTINCT Clause.

UNION, INTERSECT, and EXCEPT Operators

The UNION operator computes the set union of the rows returned by the involved SELECT statements.

The INTERSECT operator computes the set intersection of the rows returned by the involved SELECT statements.

The EXCEPT operator computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one.

See UNION, Intercept, and Except Operators.

ORDER BY Clause

The ORDER BY clause sorts the result rows by the specified expressions. See ORDER BY Clause.

LIMIT, FETCH, and OFFSET Clauses

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