> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Selection Queries

Selection queries read and process data in CockroachDB.  They are more general than [simple `SELECT` clauses](/docs/stable/select-clause): they can group one or more [selection clauses](#selection-clauses) with [set operations](#set-operations) and can request a [specific ordering](/docs/stable/order-by.md) or [row limit](/docs/stable/limit-offset.md).

Selection queries can occur:

* At the top level of a query, like other [SQL statements](/docs/stable/sql-statements.md).
* Between parentheses as a [subquery](/docs/stable/table-expressions.md#use-a-subquery).
* As [operand to other statements](#use-selection-queries-with-other-statements) that take tabular data as input, for example [`INSERT`](/docs/stable/insert), [`UPSERT`](/docs/stable/upsert),  [`CREATE TABLE AS`](/docs/stable/create-table-as), or [`ALTER ... SPLIT AT`](/docs/stable/alter-table#split-at).

## Synopsis

![select syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/select.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=9f63f0578e5322d046403c6bb04aa4aa)

## Parameters

| Parameter | Description |
| - | - |
| `common_table_expr` | See [Common Table Expressions](/docs/stable/common-table-expressions.md). |
| `select_clause` | A valid [selection clause](#selection-clauses), either simple or using [set operations](#set-operations). |
| `sort_clause` | An optional `ORDER BY` clause. See [Ordering Query Results](/docs/stable/order-by.md) for details. |
| `limit_clause` | An optional `LIMIT` clause. See [Limit Query Results](/docs/stable/limit-offset.md) for details. |
| `offset_clause` | An optional `OFFSET` clause. See [Limit Query Results](/docs/stable/limit-offset.md) for details. |
| `for_locking_clause` | The `FOR UPDATE` and `FOR SHARE` clauses are used to lock `SELECT` statements. For more information, see [`FOR UPDATE` and `FOR SHARE`](/docs/stable/select-for-update). |

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

## Selection clauses

A *selection clause* is the main component of a selection query. A selection clause
defines tabular data. There are four specific syntax forms:

| Form | Usage |
| - | - |
| [`SELECT`](#select-clause) | Load or compute tabular data from various sources. This is the most common selection clause. |
| [`VALUES`](#values-clause) | List tabular data by the client. |
| [`TABLE`](#table-clause) | Load tabular data from the database. |
| [Set operations](#set-operations) | Combine tabular data from two or more selection clauses. |

### Synopsis

![select\_clause syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/select_clause.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=2c901ed6b67a72fbdefcaa281d471fac)

### `VALUES` clause

![values\_clause syntax diagram](https://mintcdn.com/cockroachlabs/M6E8QStE4G5ISd4O/images/sql-diagrams/common/values_clause.svg?fit=max\&auto=format\&n=M6E8QStE4G5ISd4O\&q=85\&s=b4fa3ce529d3bf5dfa3ef52b62795f20)

#### 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. When the `VALUES` clause is used as a [subquery](/docs/stable/subqueries.md),
you can modify these names with [`AS`](/docs/stable/table-expressions#aliased-table-expressions).

#### Example

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> VALUES (1, 2, 3), (4, 5, 6);
```

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

### `TABLE` clause

![table\_clause syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/table_clause.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=3851188811bd3b3fad02a296b67244b0)

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

`TABLE x` is equivalent to `SELECT * FROM x`.

#### Example

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE employee_copy AS TABLE employee;
```

This statement copies the content from table `employee` into a new
table. However, 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> TABLE employee;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO employee_copy TABLE employee;
```

### `SELECT` clause

See [Simple `SELECT` Clause](/docs/stable/select-clause) for more
details.

## Set operations

Set operations combine data from two [selection
clauses](#selection-clauses). They are valid as operand to other
set operations or as main component in a selection query.

### Synopsis

![set\_operation syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/set_operation.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=d3f8f747d57773be9bd3f5e21fb59df6)

### Set operators

SQL lets you compare the results of multiple [selection clauses](#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, to display duplicate values, you can add an `ALL` to the clause.

### `UNION`: Combine two queries

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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` selects only values that are present in both query operands.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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` selects values that are present in the first query operand but not the second.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT name
FROM mortgages
EXCEPT
SELECT name
FROM accounts;
```

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

## Order results

The following sections provide examples. For more details, see [`ORDER BY`](/docs/stable/order-by).

### Order retrieved rows by one column

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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           |
+----+--------------------+---------+----------+--------------+
```

## Limit row count

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT id, name
FROM accounts
LIMIT 5;
```

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

## Row-level locking for concurrency control with `SELECT FOR UPDATE`

`SELECT ... FOR UPDATE` exclusively locks the rows returned by a [selection query][selection], such that other transactions trying to access those rows must wait for the transaction that locked the rows to commit or rollback.

`SELECT ... FOR UPDATE` can be used to:

* Strengthen the isolation of a [`READ COMMITTED`](/docs/stable/read-committed) transaction. If you need to read and later update a row within a transaction, use `SELECT ... FOR UPDATE` to acquire an exclusive lock on the row. This guarantees data integrity between the transaction's read and write operations. For details, see [Locking reads](/docs/stable/read-committed.md#locking-reads).

* Order [`SERIALIZABLE`](/docs/stable/demo-serializable) transactions by controlling concurrent access to one or more rows of a table. These other transactions are placed into a queue based on when they tried to read the values of the locked rows.

  Because this queueing happens during the read operation, the [thrashing](https://wikipedia.org/wiki/Thrashing_\(computer_science\)) that would otherwise occur if multiple concurrently executing transactions attempt to `SELECT` the same data and then `UPDATE` the results of that selection is prevented. By preventing thrashing, `SELECT ... FOR UPDATE` also prevents [transaction retries][retries] that would otherwise occur due to [contention](/docs/stable/performance-best-practices-overview.md#transaction-contention).

  As a result, using `SELECT ... FOR UPDATE` leads to increased throughput and decreased tail latency for contended operations.

Note that using `SELECT ... FOR UPDATE` does not completely eliminate the chance of [serialization errors](/docs/stable/transaction-retry-error-reference.md). These errors can also arise due to [time uncertainty](/docs/stable/architecture/transaction-layer.md#transaction-conflicts). To eliminate the need for application-level retry logic, in addition to `SELECT FOR UPDATE` your application also needs to use a [driver that implements automatic retry handling](/docs/stable/transaction-retry-error-reference.md#client-side-retry-handling).

> [!NOTE]
>
> By default, CockroachDB uses the `SELECT ... FOR UPDATE` locking mechanism during the initial row scan performed in [`UPDATE`](/docs/stable/update) and [`UPSERT`](/docs/stable/upsert) statement execution. To turn off implicit `SELECT ... FOR UPDATE` locking for `UPDATE` and `UPSERT` statements, set the `enable_implicit_select_for_update` [session variable](/docs/stable/set-vars.md) to `false`.

[retries]: /docs/v26.3/transactions.md#transaction-retries

[selection]: /docs/v26.3/selection-queries.md

For an example, see  [`FOR UPDATE` and `FOR SHARE`](/docs/stable/select-for-update).

## Composability

[Selection clauses](#selection-clauses) are defined in the context of selection queries. [Table expressions](/docs/stable/table-expressions.md) are defined in the context of the `FROM` sub-clause of [`SELECT`](/docs/stable/select-clause). Nevertheless, you can integrate them with one another to form more complex queries or statements.

### Use a selection clause as a selection query

You can use a [selection clause](#selection-clauses) as a
selection query with no change.

For example, the construct [`SELECT * FROM accounts`](/docs/stable/select-clause) 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)`](#values-clause) is also a selection
clause and thus can also be used as a selection query on its own:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> VALUES (1), (2), (3);
```

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

### Use a table expression as selection clause

You can use a [table expression](/docs/stable/table-expressions.md) 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](/docs/stable/table-expressions.md#table-and-view-names) `customers` is a table expression, which designates all rows in that table. The expressions [`TABLE accounts`](/docs/stable/selection-queries#table-clause) and [`SELECT * FROM accounts`](/docs/stable/select-clause) are valid selection clauses.

Likewise, the [SQL join expression](/docs/stable/joins.md) `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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> TABLE (customers c JOIN orders o ON c.id = o.customer_id);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id;
```

### Use a selection query as table expression

You can use a selection query (or [selection clause](#selection-clauses)) as a [table
expression](/docs/stable/table-expressions.md) by enclosing it between parentheses, which forms a
[subquery](/docs/stable/table-expressions.md#use-a-subquery).

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT id FROM (SELECT * FROM customers ORDER BY name LIMIT 5);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT o.id
    FROM orders o
    JOIN (SELECT * FROM customers ORDER BY name LIMIT 5) AS c
	  ON o.customer_id = c.id;
```

### Use 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`](/docs/stable/insert) | `INSERT INTO foo SELECT * FROM bar` | `INSERT INTO foo VALUES (1), (2), (3)` | `INSERT INTO foo TABLE bar` |
| [`UPSERT`](/docs/stable/upsert) | `UPSERT INTO foo SELECT * FROM bar` | `UPSERT INTO foo VALUES (1), (2), (3)` | `UPSERT INTO foo TABLE bar` |
| [`CREATE TABLE AS`](/docs/stable/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`](/docs/stable/alter-table#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](/docs/stable/table-expressions.md) | `SELECT * FROM (SELECT * FROM bar)` | `SELECT * FROM (VALUES (1),(2),(3))` | `SELECT * FROM (TABLE bar)` |
| Subquery in a [scalar expression](/docs/stable/scalar-expressions.md) | `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](/docs/stable/select-clause)
* [`SELECT FOR UPDATE`](/docs/stable/select-for-update)
* [Table Expressions](/docs/stable/table-expressions.md)
* [Ordering Query Results](/docs/stable/order-by.md)
* [Limit Query Results](/docs/stable/limit-offset.md)