> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Selection Queries
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
Selection queries read and process data in CockroachDB. They are more general than simple `SELECT` clauses: they can group one or more [selection clauses](#selection-clauses) with [set operations](#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](#use-selection-queries-with-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](#selection-clauses), either simple or using [set operations](#set-operations). |
| `sort\_clause` | An optional `ORDER BY` clause. See Ordering Query Results for details. |
| `limit\_clause` | An optional `LIMIT` clause. See Limit Query Results for details. |
| `offset\_clause` | An optional `OFFSET` clause. See Limit Query Results 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`. |
The optional `LIMIT` and `OFFSET` clauses can appear in any order, but if also present, must appear **after** `ORDER BY`.
Because the `WITH`, `ORDER BY`, `LIMIT`, and `OFFSET` sub-clauses are all optional, any simple [selection clause](#selection-clauses) is also a valid selection query.
## 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. |
To perform joins or other relational operations over selection clauses, use a table expression and [convert it back](#composability) into a selection clause with [`TABLE`](#table-clause) or [`SELECT`](#select-clause).
### 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. When the `VALUES` clause is used as a subquery, you can modify these names with `AS`.
#### Example
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> VALUES (1, 2, 3), (4, 5, 6);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+---------+---------+---------+
| 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.
`TABLE x` is equivalent to `SELECT * FROM x`.
Any table expression between parentheses is a valid operand for `TABLE`, not just simple table or view names.
#### 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 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 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');
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-----------------+
| 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');
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-----------------+
| 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-----------------+
| 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+------------------+
| name |
+------------------+
| Günay García |
| Karla Goddard |
| Cybele Seaver |
+------------------+
```
## Order results
The following sections provide examples. For more details, see `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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+----+--------------------+---------+----------+--------------+
| 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+----+--------------------+---------+----------+--------------+
| 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+----+------------------+
| 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, 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` 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.
* Order `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 that would otherwise occur due to 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. These errors can also arise due to time uncertainty. 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.
By default, CockroachDB uses the `SELECT... FOR UPDATE` locking mechanism during the initial row scan performed in `UPDATE` and `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 to `false`.
For an example, see `FOR UPDATE` and `FOR SHARE`.
## Composability
[Selection clauses](#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, 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` 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+----+-----------------------+---------+----------+--------------+
| 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);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+---------+
| column1 |
+---------+
| 1 |
| 2 |
| 3 |
+---------+
(3 rows)
```
### Use a table expression as selection clause
You can use a table expression 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:
```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 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:
```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` | `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
* `SELECT FOR UPDATE`
* Table Expressions
* Ordering Query Results
* Limit Query Results