SELECT

Applies to: ✅ Data 360 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:

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.

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

  • With Data 360 SQL you can write a function call as a member of the FROM list.

  • Data 360 SQL supports ORDER BY and GROUP BY to specify expressions as input-column names.

  • The clauses LIMIT and OFFSET are specific to Data 360 SQL.

  • DISTINCT ON ( ... ) is an extension of the SQL standard.