Table Expressions - CockroachDB

A table expression defines a data source in the FROM sub-clause of a or as parameter to a . A is a particular kind of table expression.

Synopsis

Parameters

Parameter	Description
`table_name`	A table or view name.
`table_alias_name`	A name to use in an aliased table expression.
`name`	One or more aliases for the column names, to use in an aliased table expression.
`index_name`	Optional syntax to force index selection.
`func_application`	Result from a function.
`row_source_extension_stmt`	Result rows from a .
`select_stmt`	A to use as subquery.
`joined_table`	A .

Table expressions language

The synopsis defines a mini-language to construct complex table expressions from simpler parts.

Construct	Description	Examples
`table_name [@ scan_parameters]`	Access a table or view.	`accounts`, `accounts@name_idx`
`function_name ( exprs ... )`	Generate tabular data using a scalar function or table generator function.	`sin(1.2)`, `generate_series(1,10)`
`[AS] name [( name [, ...] )]`	Rename a table and optionally columns.	`accounts a`, `accounts AS a`, `accounts AS a(id, b)`
`WITH ORDINALITY`	Enumerate the result rows.	`accounts WITH ORDINALITY`
`JOIN ON ...`	.	`orders o JOIN customers c ON o.customer_id = c.id`
`(... subquery ...)`	A used as subquery.	`(SELECT * FROM customers c)`
`[... statement ...]`	Use the result rows of an . This is a CockroachDB extension. However, Cockroach Labs recommends that you use the standard SQL instead. See Use the output of another statement for an example.	`[SHOW COLUMNS FROM accounts]`

The following sections provide details on each of these options.

Table expressions that generate data

The following sections describe primary table expressions that produce data.

Table and view names

Syntax

identifier
identifier.identifier
identifier.identifier.identifier

A single SQL identifier in a table expression designates the contents of the table, , or sequence with that name in the current database, as configured by . If the name is composed of two or more identifiers, rules apply. For example:

> SELECT * FROM users; -- uses table `users` in the current database

> SELECT * FROM mydb.users; -- uses table `users` in database `mydb`

Force index selection

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

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

Force index scan

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};

Force reverse scan

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

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

Forcing a reverse scan is sometimes useful during . 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 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 . 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 users@{FORCE_INDEX=primary,DESC};

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

Force partial index scan

To force a , your statement must have a WHERE clause that implies the partial index filter.

CREATE TABLE t (
  a INT,
  INDEX idx (a) WHERE a > 0);
INSERT INTO t(a) VALUES (5);
SELECT * FROM t@idx WHERE a > 0;

CREATE TABLE

Time: 13ms total (execution 12ms / network 0ms)

INSERT 1

Time: 22ms total (execution 21ms / network 0ms)

  a
-----
  5
(1 row)

Time: 1ms total (execution 1ms / network 0ms)

Force partial GIN index scan

To force a scan, your statement must have a WHERE clause that:

Implies the partial index.
Constrains the GIN index scan.

DROP TABLE t;
CREATE TABLE t (
  j JSON,
  INVERTED INDEX idx (j) WHERE j->'a' = '1');
INSERT INTO t(j)
  VALUES ('{"a": 1}'),
         ('{"a": 3, "b": 2}'),
         ('{"a": 1, "b": 2}');
SELECT * FROM t@idx WHERE j->'a' = '1' AND j->'b' = '2';

DROP TABLE

Time: 68ms total (execution 22ms / network 45ms)

CREATE TABLE

Time: 10ms total (execution 10ms / network 0ms)

INSERT 3

Time: 22ms total (execution 22ms / network 0ms)

         j
--------------------
  {"a": 1, "b": 2}
(1 row)

Time: 1ms total (execution 1ms / network 0ms)

Prevent full scan

To prevent the optimizer from planning a full scan for a table, specify the NO_FULL_SCAN index hint. For example:

SELECT * FROM table_name@{NO_FULL_SCAN};

To prevent a full scan of a partial index, you must specify NO_FULL_SCAN in combination with the partial index using FORCE_INDEX=index_name. If you specify only NO_FULL_SCAN, a full scan of a partial index may be planned.

You can also force index selection for and statements.

Access a common table expression

A single identifier in a table expression can refer to a defined earlier. For example:

> WITH a AS (SELECT * FROM users)
  SELECT * FROM a; -- "a" refers to "WITH a AS .."

Result from a function

A table expression can use the result from a function application as a data source.

Syntax

name ( arguments... )

The name of a function, followed by an opening parenthesis, followed by zero or more , followed by a closing parenthesis. The resolution of the function name follows the same rules as the resolution of table names. See for more details.

Scalar function as data source

When a is used as a table expression, it is interpreted as tabular data with a single column and single row containing the function result. For example:

> SELECT * FROM sin(3.2)

+-----------------------+
|          sin          |
+-----------------------+
| -0.058374143427580086 |
+-----------------------+

Table generator functions

Some functions directly generate tabular data with multiple rows from a single function application. This is also called a set-returning function (SRF). For example:

> SELECT * FROM generate_series(1, 3);

+-----------------+
| generate_series |
+-----------------+
|               1 |
|               2 |
|               3 |
+-----------------+

You access SRFs using (SRF).x where x is one of the following:

The name of a column returned from the function.
*, to denote all columns.

For example (the output of queries against will vary per database):

> SELECT (i.keys).* FROM (SELECT information_schema._pg_expandarray(indkey) AS keys FROM pg_index) AS i;

 x | n
---+---
 1 | 1
 2 | 1
(2 rows)

CockroachDB supports the generator functions compatible with the PostgreSQL set-generating functions with the same names.

Operators that extend a table expression

The following sections describe table expressions that change the metadata around tabular data, or add more data, without modifying the data of the underlying operand.

Aliased table expressions

Aliased table expressions rename tables and columns temporarily in the context of the current query.

Syntax

 AS <name>
 AS <name>(<colname, <colname>, ...)

In the first form, the table expression is equivalent to its left operand with a new name for the entire table, and where columns retain their original name. In the second form, the columns are also renamed. For example:

> SELECT c.x FROM (SELECT COUNT(*) AS x FROM users) AS c;

> SELECT c.x FROM (SELECT COUNT(*) FROM users) AS c(x);

Ordinality annotation

Appends a column named ordinality, whose values describe the ordinality of each row, to the data source specified in the table expression operand.

Syntax

 WITH ORDINALITY

For example:

> SELECT * FROM (VALUES('a'),('b'),('c'));

+---------+
| column1 |
+---------+
| a       |
| b       |
| c       |
+---------+

> SELECT * FROM (VALUES ('a'), ('b'), ('c')) WITH ORDINALITY;

+---------+------------+
| column1 | ordinality |
+---------+------------+
| a       |          1 |
| b       |          2 |
| c       |          3 |
+---------+------------+

WITH ORDINALITY necessarily prevents some optimizations of the surrounding query. Use it sparingly if performance is a concern, and always check the output of in case of doubt.

`JOIN` expressions

JOIN expressions combine the results of two or more table expressions based on conditions on the values of particular columns. See for more details.

Use another query as a table expression

The following sections describe how to use the result produced by another SQL query or statement as a table expression.

Use a subquery

You can use a enclosed between parentheses () as a table expression. This is called a .

Syntax

( ... subquery ... )

For example:

> SELECT c+2 FROM (SELECT COUNT(*) AS c FROM users);

> SELECT * FROM (VALUES(1), (2), (3));

> SELECT firstname || ' ' || lastname FROM (TABLE employees);

See for more details and performance best practices.
To use other statements that produce data in a table expression, for example SHOW, see Use the output of another statement.

Use the output of another statement

Syntax

WITH table_expr AS ( <stmt> ) SELECT .. FROM table_expr

A designates the output of executing a statement as a row source. The following statements are supported as row sources for table expressions:

For example:

> WITH x AS (SHOW COLUMNS from customer) SELECT "column_name" FROM x;

+-------------+
| column_name |
+-------------+
| id          |
| name        |
| address     |
+-------------+
(3 rows)

The following statement inserts Albert in the employee table and immediately creates a matching entry in the management table with the auto-generated employee ID, without requiring a round trip with the SQL client:

> INSERT INTO management(manager, reportee)
    VALUES ((SELECT id FROM employee WHERE name = 'Diana'),
            (WITH x AS (INSERT INTO employee(name) VALUES ('Albert') RETURNING id) SELECT id FROM x));

Composability

You can use table expressions in the and variants of . Thus they can appear everywhere where a selection clause is possible. For example:

> SELECT ... FROM , , ...
> TABLE
> INSERT INTO ... SELECT ... FROM , , ...
> INSERT INTO ... TABLE
> CREATE TABLE ... AS SELECT ... FROM , , ...
> UPSERT INTO ... SELECT ... FROM , , ...

For more options to compose query results, see .

​Synopsis

​Parameters

​Table expressions language

​Table expressions that generate data

​Table and view names

​Syntax

​Force index selection

Force index scan

Force reverse scan

Force partial index scan

Force partial GIN index scan

Prevent full scan

​Access a common table expression

​Result from a function

​Syntax

​Scalar function as data source

​Table generator functions

​Operators that extend a table expression

​Aliased table expressions

​Syntax

​Ordinality annotation

​Syntax

​JOIN expressions

​Use another query as a table expression

​Use a subquery

​Syntax

​Use the output of another statement

​Syntax

​Composability

​See also

Synopsis

Parameters

Table expressions language

Table expressions that generate data

Table and view names

Syntax

Force index selection

Access a common table expression

Result from a function

Syntax

Scalar function as data source

Table generator functions

Operators that extend a table expression

Aliased table expressions

Syntax

Ordinality annotation

Syntax

`JOIN` expressions

Use another query as a table expression

Use a subquery

Syntax

Use the output of another statement

Syntax

Composability

See also