Selection Queries

Selection queries read and process data in CockroachDB. They are moregeneral than simple SELECT clauses: they cangroup one or more selection clauses with setoperations and can request a specificordering 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

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. Theydefine tabular data. There are four specific syntax forms collectively named 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 expressionslisted within parentheses. Each parenthesis group defines a single rowin the resulting table.

The columns of the resulting table data have automatically generatednames. These names can be modified withAS when theVALUES 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. Thecolumns of the resulting table data are named after the schema of thetable.

In general, TABLE x is equivalent to SELECT * FROM x, but it isshorter 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 newtable. However, note that the TABLE clause does not preserve the indexing,foreign key, or constraint and default information from the schema of thetable it reads from, so in this example, the new table employee_copywill likely have a simpler schema than employee.

Other examples:

> TABLE employee;

> INSERT INTO employee_copy TABLE employee;

SELECT clause

See Simple SELECT Clause for moredetails.

Set operations

Set operations combine data from two selectionclauses. They are valid as operand to otherset 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 aselection 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 selectionclause 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 tableexpression by enclosing it between parentheses, which forms asubquery.

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:

See also

• Simple SELECT Clause
• Table Expressions
• Ordering Query Results
• Limiting Query Results

Was this page helpful?
YesNo