Selection Queries

Selection queries read and process data in CockroachDB. They are more general than : they can group one or more selection clauses with set operations and can request a or .

Selection queries can occur:

• At the top level of a query, like other .
• Between parentheses as a .
• As operand to other statements that take tabular data as input, for example , , , or .

Synopsis

Parameters

Parameter	Description
common_table_expr	See .
select_clause	A valid selection clause, either simple or using set operations.
sort_clause	An optional ORDER BY clause. See for details.
limit_clause	An optional LIMIT clause. See for details.
offset_clause	An optional OFFSET clause. See for details.
for_locking_clause	The FOR UPDATE and FOR SHARE clauses are used to lock SELECT statements. For more information, see .

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

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 , you can modify these names with .

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.

TABLE x is equivalent to SELECT * FROM x.

Example

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

> TABLE employee;

> INSERT INTO employee_copy TABLE employee;

SELECT clause

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

> 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 selects 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 selects 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    |
+------------------+

Order results

The following sections provide examples. For more details, see .

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

Limit row count

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

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

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

  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 . These errors can also arise due to . To eliminate the need for application-level retry logic, in addition to SELECT FOR UPDATE your application also needs to use a .

By default, CockroachDB uses the SELECT ... FOR UPDATE locking mechanism during the initial row scan performed in and statement execution. To turn off implicit SELECT ... FOR UPDATE locking for UPDATE and UPSERT statements, set the enable_implicit_select_for_update to false.

For an example, see .

Composability

Selection clauses are defined in the context of selection queries. are defined in the context of the FROM sub-clause of . 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 as a selection query with no change.

For example, the construct 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)

Use a table expression as selection clause

You can use a 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 customers is a table expression, which designates all rows in that table. The expressions and are valid selection clauses.

Likewise, the 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;

Use a selection query as table expression

You can use a selection query (or selection clause) as a by enclosing it between parentheses, which forms a .

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;

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 INTO foo SELECT * FROM bar	INSERT INTO foo VALUES (1), (2), (3)	INSERT INTO foo TABLE bar
	UPSERT INTO foo SELECT * FROM bar	UPSERT INTO foo VALUES (1), (2), (3)	UPSERT INTO foo TABLE bar
	CREATE TABLE foo AS SELECT * FROM bar CREATE TABLE foo AS VALUES (1),(2),(3)	CREATE TABLE foo AS TABLE bar
	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	SELECT * FROM (SELECT * FROM bar)	SELECT * FROM (VALUES (1),(2),(3))	SELECT * FROM (TABLE bar)
Subquery in a	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