SELECT Clauses

On this page Carat arrow pointing down
Warning:
CockroachDB v1.1 is no longer supported as of April 12, 2019. For more details, refer to the Release Support Policy.

SELECT clauses define tabular data. They can be used either as standalone statements, of which SELECT is the most common, or as subqueries in table expressions.

Overview

SQL is fundamentally about manipulating tabular data, and SELECT clauses are the primary way to obtain tabular data for manipulation.

There are three specific syntax forms collectively named SELECT clauses:

Form Usage
VALUES List tabular data by the client.
TABLE Load tabular data from the database.
SELECT Load or compute tabular data from various sources.

SELECT clauses appear in every statement that takes tabular data as input, for example:

Possible Combinations

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
SELECT SELECT * FROM (SELECT * FROM bar) SELECT * FROM (VALUES (1),(2),(3)) SELECT * FROM (TABLE bar)
Expression subquery 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)

VALUES Clause

Syntax

VALUES (<expr>, <expr>, ...), (<expr>, <expr>, ...), ...

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. These names can be modified with AS when the VALUES clause is used as a sub-query.

Example

icon/buttons/copy
> VALUES (1, 2, 3), (4, 5, 6);
+---------+---------+---------+
| column1 | column2 | column3 |
+---------+---------+---------+
|       1 |       2 |       3 |
|       4 |       5 |       6 |
+---------+---------+---------+

TABLE Clause

Syntax

TABLE <tablename>

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.

In general, TABLE x is equivalent to SELECT * FROM x, but it is shorter to type.

Example

icon/buttons/copy
> CREATE TABLE employee_copy AS TABLE employee;

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

icon/buttons/copy
> TABLE employee;
icon/buttons/copy
> INSERT INTO employee_copy TABLE employee;

SELECT Clause

The SELECT clause is documented separately as the stand-alone SELECT statement, which is its most common use in SQL. However, it's important to note the broader application of SELECT clauses, which can be used alongside the other two selection clause forms everywhere such a clause is admissible.

See Also


Yes No
On this page

Yes No