# `SELECT`

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

Retrieves data from a table or view.

## Syntax

`SELECT` query syntax uses the required `SELECT` statement with one or more optional clauses.

```sql
[WITH [RECURSIVE] <with_query> [, ...]]
SELECT [ALL | DISTINCT [ON (<expression> [, ...])]]
    [* | <expression> [[AS] <output_name>] [, ...]]
    [FROM <from_item> [, ...]]
    [WHERE <condition>]
    [GROUP BY { ALL | [ALL | DISTINCT] <grouping_element> [, ...] }]
    [HAVING <condition> [, ...]]
    [WINDOW <window_name> AS (<window_definition>) [, ...]]
    [{ UNION | INTERSECT | EXCEPT } [ALL | DISTINCT] <select>]
    [ORDER BY { ALL [ASC | DESC] [NULLS { FIRST | LAST }] | <expression> [ASC | DESC] [NULLS { FIRST | LAST }] [, ...] }]
    [LIMIT { <count> | ALL }]
    [OFFSET <start> [ROW | ROWS]]
    [FETCH { <FIRST> | <NEXT> } [<count>] { ROW | ROWS } { ONLY | WITH TIES }];
```

`with_query` is defined as:

```sql
<with_query_name> [(<column_name> [, ...])] AS (<select>)
```

`from_item` is one of:

```sql
<table_name> [[AS] <alias> [(<column_alias> [, ...])]]

<with_query_name> [[AS] <alias> [(<column_alias> [, ...])]]

[LATERAL] (<select>) [AS] [<alias>] [(<column_alias> [, ...])]

[LATERAL] <function_name> ([<argument> [, ...]])
    [[AS] <alias> [(<column_alias> [, ...])]]

<from_item> [NATURAL] <join_type> <from_item>
    [ON <join_condition> | USING (<join_column> [, ...])]
```

`grouping_element` is defined as:

```sql
()
<expression>
(<expression>s [, ...])
ROLLUP ({ <expression> | (<expression> [, ...])} [, ...])
CUBE ({ <expression> | (<expression> [, ...])} [, ...])
GROUPING SETS (<grouping_element> [, ...])
```

## Processing Rules

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](/docs/sql/aggregate) 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.

## Clauses

- `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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-with.md).

- `FROM` Clause

  - : The `FROM` clause specifies one or more source tables for the `SELECT`. See [FROM Clause](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-from.md).

- `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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-group-by.md).

- `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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-window.md).

- `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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-list.md) and [DISTINCT Clause](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-list.md#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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-union.md).

- `ORDER BY` Clause

  - : The `ORDER BY` clause sorts the result rows by the specified expressions. See [ORDER BY Clause](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-order.md).

- `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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-limit-offset.md).

## Compatibility

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.

  ```sql
  SELECT 2+2;
  ```

  If you don’t specify a `FROM` clause, the query can’t reference any database tables. For example, this query is invalid.

  ```sql
  SELECT distributors.* WHERE distributors.name = 'Westward';
  ```

<!---->

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