# Selection Queries

Selection queries read and process data in CockroachDB. They are more general than simple `SELECT` clauses: they can group one or more selection clauses with set operations and can request a specific ordering or row limit.

Selection queries can occur:

- At the top level of a query like other SQL statements.
- Between parentheses as a subquery.
- As operand to other statements that take tabular data as input, for example `INSERT`, `UPSERT`, `CREATE TABLE AS` or `ALTER ... SPLIT AT`.

## Synopsis

## Parameters

Parameter | Description
--- | ---
`common_table_expr` | See Common Table Expressions.
`select_clause` | A valid selection clause, either simple or using set operations.
`sort_clause` | An optional `ORDER BY` clause. See Ordering Query Results for details.
`limit_clause` | An optional `LIMIT` clause. See Limiting Query Results for details.
`offset_clause` | An optional `OFFSET` clause. See Limiting Query Results for details.

The optional `LIMIT` and `OFFSET` clauses can appear in any order, but must appear after `ORDER BY`, if also present.

Note:
Because the `WITH`, `ORDER BY`, `LIMIT` and `OFFSET` sub-clauses are all optional, any simple selection clause is also a valid selection query.

## Selection clauses

Selection clauses are the main component of a selection query. They define tabular data. There are four specific syntax forms collectively named selection clauses:

Form | Usage
--- | ---
`SELECT` | Load or compute tabular data from various sources. This is the most common selection clause.
`VALUES` | List tabular data by the client.
`TABLE` | Load tabular data from the database.
Set Operations | Combine tabular data from two or more selection clauses.

Note:
To perform joins or other relational operations over selection clauses, use a table expression and convert it back into a selection clause with `TABLE` or `SELECT`.

### Synopsis

### `VALUES` clause

#### Syntax

A `VALUES` clause defines tabular data defined by the expressions listed within parentheses. Each parenthesis group defines a single row in the resulting table.

The columns of the resulting table data have automatically generated names. These names can be modified with `AS` when the `VALUES` clause is used as a sub-query.

#### Example

    > VALUES (1, 2, 3), (4, 5, 6);

    +---------+---------+---------+
    | column1 | column2 | column3 |
    +---------+---------+---------+
    |       1 |       2 |       3 |
    |       4 |       5 |       6 |
    +---------+---------+---------+

### `TABLE` clause

#### Syntax

A `TABLE` clause reads tabular data from a specified table. The columns of the resulting table data are named after the schema of the table.

In general, `TABLE x` is equivalent to `SELECT * FROM x`, but it is shorter to type.

Note:
Any table expression between parentheses is a valid operand for `TABLE`, not just simple table or view names.

#### Example

    > CREATE TABLE employee_copy AS TABLE employee;

This statement copies the content from table `employee` into a new table. However, note that the `TABLE` clause does not preserve the indexing, foreign key, or constraint and default information from the schema of the table it reads from, so in this example, the new table `employee_copy` will likely have a simpler schema than `employee`.

Other examples:

    > TABLE employee;

    > INSERT INTO employee_copy TABLE employee;

### `SELECT` clause

See Simple `SELECT` Clause for more details.

## Set operations

Set operations combine data from two selection clauses. They are valid as operand to other set operations or as main component in a selection query.

### Synopsis

### Set operators

SQL lets you compare the results of multiple selection clauses. You can think of each of the set operators as representing a Boolean operator:

- `UNION` = OR
- `INTERSECT` = AND
- `EXCEPT` = NOT

By default, each of these comparisons displays only one copy of each value (similar to `SELECT DISTINCT`). However, each function also lets you add an `ALL` to the clause to display duplicate values.

### Union: Combine two queries

`UNION` combines the results of two queries into one result.

    > SELECT name
    FROM accounts
    WHERE state_opened IN ('AZ', 'NY')
    UNION
    SELECT name
    FROM mortgages
    WHERE state_opened IN ('AZ', 'NY');

    +-----------------+
    |      name       |
    +-----------------+
    | Naseem Joossens |
    | Ricarda Caron   |
    | Carola Dahl     |
    | Aygün Sanna     |
    +-----------------+

To show duplicate rows, you can use `ALL`.

    > SELECT name
    FROM accounts
    WHERE state_opened IN ('AZ', 'NY')
    UNION ALL
    SELECT name
    FROM mortgages
    WHERE state_opened IN ('AZ', 'NY');

    +-----------------+
    |      name       |
    +-----------------+
    | Naseem Joossens |
    | Ricarda Caron   |
    | Carola Dahl     |
    | Naseem Joossens |
    | Aygün Sanna     |
    | Carola Dahl     |
    +-----------------+

### Intersect: Retrieve intersection of two queries

`INTERSECT` finds only values that are present in both query operands.

    > SELECT name
    FROM accounts
    WHERE state_opened IN ('NJ', 'VA')
    INTERSECT
    SELECT name
    FROM mortgages;

    +-----------------+
    |      name       |
    +-----------------+
    | Danijel Whinery |
    | Agar Archer     |
    +-----------------+

### Except: Exclude one query's results from another

`EXCEPT` finds values that are present in the first query operand but not the second.

    > SELECT name
    FROM mortgages
    EXCEPT
    SELECT name
    FROM accounts;

    +------------------+
    |       name       |
    +------------------+
    | Günay García     |
    | Karla Goddard    |
    | Cybele Seaver    |
    +------------------+

## Ordering results

The following sections provide examples. For more details, see Ordering Query Results.

### Order retrieved rows by one column

    > SELECT *
    FROM accounts
    WHERE balance BETWEEN 350 AND 500
    ORDER BY balance DESC;

    +----+--------------------+---------+----------+--------------+
    | id |        name        | balance |   type   | state_opened |
    +----+--------------------+---------+----------+--------------+
    | 12 | Raniya Žitnik      |     500 | savings  | CT           |
    | 59 | Annibale Karga     |     500 | savings  | ND           |
    | 27 | Adelbert Ventura   |     500 | checking | IA           |
    | 86 | Theresa Slaski     |     500 | checking | WY           |
    | 73 | Ruadh Draganov     |     500 | checking | TN           |
    | 16 | Virginia Ruan      |     400 | checking | HI           |
    | 43 | Tahirih Malinowski |     400 | checking | MS           |
    | 50 | Dusan Mallory      |     350 | savings  | NV           |
    +----+--------------------+---------+----------+--------------+

### Order retrieved rows by multiple columns

Columns are sorted in the order you list them in `sortby_list`. For example, `ORDER BY a, b` sorts the rows by column `a` and then sorts rows with the same `a` value by their column `b` values.

    > SELECT *
    FROM accounts
    WHERE balance BETWEEN 350 AND 500
    ORDER BY balance DESC, name ASC;

    +----+--------------------+---------+----------+--------------+
    | id |        name        | balance |   type   | state_opened |
    +----+--------------------+---------+----------+--------------+
    | 27 | Adelbert Ventura   |     500 | checking | IA           |
    | 59 | Annibale Karga     |     500 | savings  | ND           |
    | 12 | Raniya Žitnik      |     500 | savings  | CT           |
    | 73 | Ruadh Draganov     |     500 | checking | TN           |
    | 86 | Theresa Slaski     |     500 | checking | WY           |
    | 43 | Tahirih Malinowski |     400 | checking | MS           |
    | 16 | Virginia Ruan      |     400 | checking | HI           |
    | 50 | Dusan Mallory      |     350 | savings  | NV           |
    +----+--------------------+---------+----------+--------------+

## Limiting row count and pagination

The following sections provide examples. For more details, see Limiting Query Results.

### Limit number of retrieved results

You can reduce the number of results with `LIMIT`.

    > SELECT id, name
    FROM accounts
    LIMIT 5;

    +----+------------------+
    | id |       name       |
    +----+------------------+
    |  1 | Bjorn Fairclough |
    |  2 | Bjorn Fairclough |
    |  3 | Arturo Nevin     |
    |  4 | Arturo Nevin     |
    |  5 | Naseem Joossens  |
    +----+------------------+

### Paginate through limited results

If you want to limit the number of results, but go beyond the initial set, use `OFFSET` to proceed to the next set of results. This is often used to paginate through large tables where not all of the values need to be immediately retrieved.

    > SELECT id, name
    FROM accounts
    LIMIT 5
    OFFSET 5;

    +----+------------------+
    | id |       name       |
    +----+------------------+
    |  6 | Juno Studwick    |
    |  7 | Juno Studwick    |
    |  8 | Eutychia Roberts |
    |  9 | Ricarda Moriarty |
    | 10 | Henrik Brankovic |
    +----+------------------+

## Composability

Selection clauses are defined in the context of selection queries. Table expressions are defined in the context of the `FROM` sub-clause of `SELECT`. Nevertheless, they can be integrated with one another to form more complex queries or statements.

### Using any selection clause as a selection query

Any selection clause can be used as a selection query with no change.

For example, the construct `SELECT * FROM accounts` is a selection clause. It is also a valid selection query, and thus can be used as a stand-alone statement by appending a semicolon:

    > SELECT * FROM accounts;

    +----+-----------------------+---------+----------+--------------+
    | id |         name          | balance |   type   | state_opened |
    +----+-----------------------+---------+----------+--------------+
    |  1 | Bjorn Fairclough      |    1200 | checking | AL           |
    |  2 | Bjorn Fairclough      |    2500 | savings  | AL           |
    |  3 | Arturo Nevin          |     250 | checking | AK           |
    [ truncated ]
    +----+-----------------------+---------+----------+--------------+

Likewise, the construct `VALUES (1), (2), (3)` is also a selection clause and thus can also be used as a selection query on its own:

    > VALUES (1), (2), (3);

    +---------+
    | column1 |
    +---------+
    |       1 |
    |       2 |
    |       3 |
    +---------+
    (3 rows)

### Using any table expression as selection clause

Any table expression can be used as a selection clause (and thus also a selection query) by prefixing it with `TABLE` or by using it as an operand to `SELECT * FROM`.

For example, the simple table name `customers` is a table expression, which designates all rows in that table. The expressions `TABLE accounts` and `SELECT * FROM accounts` are valid selection clauses.

Likewise, the SQL join expression `customers c JOIN orders o ON c.id = o.customer_id` is a table expression. You can turn it into a valid selection clause, and thus a valid selection query as follows:

    > TABLE (customers c JOIN orders o ON c.id = o.customer_id);

    > SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id;

### Using any selection query as table expression

Any selection query (or selection clause) can be used as a table expression by enclosing it between parentheses, which forms a subquery.

For example, the following construct is a selection query, but is not a valid table expression:

    > SELECT * FROM customers ORDER BY name LIMIT 5

To make it valid as operand to `FROM` or another table expression, you can enclose it between parentheses as follows:

    > SELECT id FROM (SELECT * FROM customers ORDER BY name LIMIT 5);

    > SELECT o.id
        FROM orders o
        JOIN (SELECT * FROM customers ORDER BY name LIMIT 5) AS c
          ON o.customer_id = c.id;

### Using selection queries with other statements

Selection queries are also valid as operand in contexts that require tabular data.

For example:

Statement | Example using `SELECT` | Example using `VALUES` | Example using `TABLE`
--- | --- | --- | ---
`INSERT` | `INSERT INTO foo SELECT * FROM bar` | `INSERT INTO foo VALUES (1), (2), (3)` | `INSERT INTO foo TABLE bar`
`UPSERT` | `UPSERT INTO foo SELECT * FROM bar` | `UPSERT INTO foo VALUES (1), (2), (3)` | `UPSERT INTO foo TABLE bar`
`CREATE TABLE AS` | `CREATE TABLE foo AS SELECT * FROM bar` `CREATE TABLE foo AS VALUES (1),(2),(3)` | `CREATE TABLE foo AS TABLE bar` |
`ALTER ... SPLIT AT` | `ALTER TABLE foo SPLIT AT SELECT * FROM bar` `ALTER TABLE foo SPLIT AT VALUES (1),(2),(3)` | `ALTER TABLE foo SPLIT AT TABLE bar` |
Subquery in a table expression | `SELECT * FROM (SELECT * FROM bar)` | `SELECT * FROM (VALUES (1),(2),(3))` | `SELECT * FROM (TABLE bar)`
Subquery in a scalar expression | `SELECT * FROM foo WHERE x IN (SELECT * FROM bar)` | `SELECT * FROM foo WHERE x IN (VALUES (1),(2),(3))` | `SELECT * FROM foo WHERE x IN (TABLE bar)`

## See also

- Simple `SELECT` Clause
- Table Expressions
- Ordering Query Results
- Limiting Query Results