Simple SELECT Clause

The simple SELECT clause is the main SQL syntax to read and processexisting data.

When used as a stand-alone statement, the simple SELECT clause isalso called "the SELECT statement". However, it is also aselection clause that can be combinedwith other constructs to form more complex selection queries.

Synopsis

Tip:

The simple SELECT clause also has other applications not covered here, such as executing functions like SELECT current_timestamp();.

Required privileges

The user must have the SELECT privilege on the tables used as operands.

Parameters

Eliminate duplicate rows

The DISTINCT subclause specifies to remove duplicate rows.

By default, or when ALL is specified, SELECT returns all the rowsselected, without removing duplicates. When DISTINCT is specified,duplicate rows are eliminated.

Without ON, two rows are considered duplicates if they are equal onall the results computed by SELECT.

With ON, two rows are considered duplicates if they are equal onlyusing the scalar expressions listed with ON. When two rows are considered duplicates according to DISTINCT ON, the values from the first FROM row in the order specified by ORDER BY are used to compute the remaining target expressions. If ORDER BY is not specified, CockroachDB will pick any one of the duplicate rows as first row, non-deterministically.

Examples

Choose columns

Retrieve specific columns

Retrieve specific columns by naming them in a comma-separated list:

> SELECT id, name, balance
FROM accounts;

+----+-----------------------+---------+
| id |         name          | balance |
+----+-----------------------+---------+
|  1 | Bjorn Fairclough      |    1200 |
|  2 | Bjorn Fairclough      |    2500 |
|  3 | Arturo Nevin          |     250 |
[ truncated ]
+----+-----------------------+---------+

Retrieve all columns

Retrieve all columns by using *:

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

Filter rows

Filter on a single condition

Filter rows with expressions that use columns and return Boolean values in the WHERE clause:

> SELECT name, balance
FROM accounts
WHERE balance < 300;

+------------------+---------+
|       name       | balance |
+------------------+---------+
| Arturo Nevin     |     250 |
| Akbar Jinks      |     250 |
| Andrea Maas      |     250 |
+------------------+---------+

Filter on multiple conditions

To use multiple WHERE filters join them with AND or OR. You can also create negative filters with NOT:

> SELECT *
FROM accounts
WHERE balance > 2500 AND NOT type = 'checking';

+----+-------------------+---------+---------+--------------+
| id |       name        | balance |  type   | state_opened |
+----+-------------------+---------+---------+--------------+
|  4 | Tullia Romijnders |    3000 | savings | AK           |
| 62 | Ruarc Mathews     |    3000 | savings | OK           |
+----+-------------------+---------+---------+--------------+

Select distinct rows

Columns without the Primary Key or Unique constraints can have multiple instances of the same value:

> SELECT name
FROM accounts
WHERE state_opened = 'VT';

+----------------+
|      name      |
+----------------+
| Sibylla Malone |
| Sibylla Malone |
+----------------+

Using DISTINCT, you can remove all but one instance of duplicate values from your retrieved data:

> SELECT DISTINCT name
FROM accounts
WHERE state_opened = 'VT';

+----------------+
|      name      |
+----------------+
| Sibylla Malone |
+----------------+

Filter values with a list

Using WHERE <column> IN (<comma separated list of values>) performs an OR search for listed values in the specified column:

> SELECT name, balance, state_opened
FROM accounts
WHERE state_opened IN ('AZ', 'NY', 'WA');

+-----------------+---------+--------------+
|      name       | balance | state_opened |
+-----------------+---------+--------------+
| Naseem Joossens |     300 | AZ           |
| Aygün Sanna     |     900 | NY           |
| Carola Dahl     |     800 | NY           |
| Edna Barath     |     750 | WA           |
| Edna Barath     |    2200 | WA           |
+-----------------+---------+--------------+

Rename columns in output

Instead of outputting a column's name in the retrieved table, you can change its label using AS:

> SELECT name AS NY_accounts, balance
FROM accounts
WHERE state_opened = 'NY';

+-------------+---------+
| NY_accounts | balance |
+-------------+---------+
| Aygün Sanna |     900 |
| Carola Dahl |     800 |
+-------------+---------+

This does not change the name of the column in the table. To do that, use RENAME COLUMN.

Search for string values

Search for partial string matches in columns using LIKE, which supports the following wildcard operators:

• % matches 0 or more characters.
• _ matches exactly 1 character.
  For example:

> SELECT id, name, type
FROM accounts
WHERE name LIKE 'Anni%';

+----+----------------+----------+
| id |      name      |   type   |
+----+----------------+----------+
| 58 | Annibale Karga | checking |
| 59 | Annibale Karga | savings  |
+----+----------------+----------+

Aggregate functions

Aggregate functions perform calculations on retrieved rows.

Perform aggregate function on entire column

By using an aggregate function as a target_elem, you can perform the calculation on the entire column.

> SELECT MIN(balance)
FROM accounts;

+--------------+
| MIN(balance) |
+--------------+
|          250 |
+--------------+

You can also use the retrieved value as part of an expression. For example, you can use the result in the WHERE clause to select additional rows that were not part of the aggregate function itself:

> SELECT id, name, balance
FROM accounts
WHERE balance = (
      SELECT
      MIN(balance)
      FROM accounts
);

+----+------------------+---------+
| id |       name       | balance |
+----+------------------+---------+
|  3 | Arturo Nevin     |     250 |
| 10 | Henrik Brankovic |     250 |
| 26 | Odalys Ziemniak  |     250 |
| 35 | Vayu Soun        |     250 |
+----+------------------+---------+

Perform aggregate function on retrieved rows

By filtering the statement, you can perform the calculation only on retrieved rows:

> SELECT SUM(balance)
FROM accounts
WHERE state_opened IN ('AZ', 'NY', 'WA');

+--------------+
| SUM(balance) |
+--------------+
|         4950 |
+--------------+

Filter columns fed into aggregate functions

You can use FILTER (WHERE <Boolean expression>) in the target_elem to filter which rows are processed by an aggregate function; those that return FALSE or NULL for the FILTER clause's Boolean expression are not fed into the aggregate function:

> SELECT count(*) AS unfiltered, count(*) FILTER (WHERE balance > 1500) AS filtered FROM accounts;

+------------+----------+
| unfiltered | filtered |
+------------+----------+
|         84 |       14 |
+------------+----------+

Create aggregate groups

Instead of performing aggregate functions on an the entire set of retrieved rows, you can split the rows into groups and then perform the aggregate function on each of them.

When creating aggregate groups, each column used as a target_elem must be included in GROUP BY.

For example:

> SELECT state_opened AS state, SUM(balance) AS state_balance
FROM accounts
WHERE state_opened IN ('AZ', 'NY', 'WA')
GROUP BY state_opened;

+-------+---------------+
| state | state_balance |
+-------+---------------+
| AZ    |           300 |
| NY    |          1700 |
| WA    |          2950 |
+-------+---------------+

Filter aggregate groups

To filter aggregate groups, use HAVING, which is the equivalent of the WHERE clause for aggregate groups, which must evaluate to a Boolean value.

For example:

> SELECT state_opened, AVG(balance) as avg
FROM accounts
GROUP BY state_opened
HAVING AVG(balance) BETWEEN 1700 AND 50000;

+--------------+---------+
| state_opened |   avg   |
+--------------+---------+
| AR           | 3700.00 |
| UT           | 1750.00 |
| OH           | 2500.00 |
| AL           | 1850.00 |
+--------------+---------+

Use aggregate functions in having clause

Aggregate functions can also be used in the HAVING clause without needing to be included as a target_elem.

For example:

> SELECT name, state_opened
FROM accounts
WHERE state_opened in ('LA', 'MO')
GROUP BY name, state_opened
HAVING COUNT(name) > 1;

+----------------+--------------+
|      name      | state_opened |
+----------------+--------------+
| Yehoshua Kleid | MO           |
+----------------+--------------+

Select from a specific index

By using the explicit index annotation, you can override CockroachDB's index selection and use a specific index when reading from a named table.

Note:

Index selection can impact performance, but does not change the result of a query.

The syntax to force a scan of a specific index is:

> SELECT * FROM table@my_idx;

This is equivalent to the longer expression:

> SELECT * FROM table@{FORCE_INDEX=my_idx};

New in v19.1: The syntax to force a reverse scan of a specific index is:

> SELECT * FROM table@{FORCE_INDEX=my_idx,DESC};

Forcing a reverse can is sometimes useful during performance tuning. For reference, the full syntax for choosing an index and its scan direction is

SELECT * FROM table@{FORCE_INDEX=idx[,DIRECTION]}

where the optional DIRECTION is either ASC (ascending) or DESC (descending).

When a direction is specified, that scan direction is forced; otherwise the cost-based optimizer is free to choose the direction it calculates will result in the best performance.

You can verify that the optimizer is choosing your desired scan direction using EXPLAIN (OPT). For example, given the table

> CREATE TABLE kv (K INT PRIMARY KEY, v INT);

you can check the scan direction with:

> EXPLAIN (opt) SELECT * FROM kv@{FORCE_INDEX=primary,DESC};

                text

scan kv,rev
└── flags: force-index=primary,rev
(2 rows)


To see all indexes available on a table, use SHOW INDEXES.

Select historical data (time-travel)

CockroachDB lets you find data as it was stored at a given point intime using AS OF SYSTEM TIME with various supportedformats. This can be also advantageous forperformance. For more details, see AS OF SYSTEM
TIME.

Advanced uses of SELECT clauses

CockroachDB supports numerous ways to combine results from SELECTclauses together.

See Selection Queries fordetails. A few examples follow.

Sorting and limiting query results

To order the results of a SELECT clause or limit the number of rowsin the result, you can combine it with ORDER BY or LIMIT /OFFSET to form a selection query orsubquery.

See Ordering Query Results and Limiting QueryResults for more details.

Note:
When ORDER BY is not included in a query, rows are not sorted by any consistent criteria. Instead, CockroachDB returns them as the coordinating node receives them.Also, CockroachDB sorts NULL values first with ASC and last with DESC. This differs from PostgreSQL, which sorts NULL values last with ASC and first with DESC.

Combining results from multiple queries

Results from two or more queries can be combined together as follows:

• Using join expressions to combine rowsaccording to conditions on specific columns.
• Using set operations to combine rowsusing inclusion/exclusion rules.

See also

• Scalar Expressions
• Selection Clauses
• Set Operations
• Table Expressions
• Ordering Query Results
• Limiting Query Results
• SQL Performance Best Practices

Was this page helpful?
YesNo