Table Expressions

Table expressions define a data source in the FROM sub-clause ofsimple SELECT clauses, or as parameter toTABLE.

SQL Joins are a particular kind of tableexpression.

Synopsis

Parameters

Table expressions language

The synopsis above really defines a mini-language to constructcomplex table expressions from simpler parts.

The following sections provide details on each of these options.

Table expressions that generate data

The following sections describe primary table expressions that producedata.

Access a table or view

Table or view names

Syntax:

identifier
identifier.identifier
identifier.identifier.identifier

A single SQL identifier in a table expression context designatesthe contents of the table, view, or sequence with that namein the current database, as configured by SET DATABASE.

If the name is composed of two or more identifiers, name resolution 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 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.

Access a common table expression

A single identifier in a table expression context can refer to acommon table expression definedearlier.

For example:

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

Results from a function

A table expression can use the results from a function application asa data source.

Syntax:

name ( arguments... )

The name of a function, followed by an opening parenthesis, followedby zero or more scalar expressions, followed bya closing parenthesis.

The resolution of the function name follows the same rules as theresolution of table names. See NameResolution for more details.

Scalar function as data source

When a function returning a singlevalue isused as a table expression, it is interpreted as tabular data with asingle column and single row containing the function results.

For example:

> SELECT * FROM sin(3.2)

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

Note:
CockroachDB only supports this syntax for compatibility with PostgreSQL. The canonical syntax to evaluate scalar functions is as a direct target of SELECT, for example SELECT sin(3.2).

Table generator functions

Some functions directly generate tabular data with multiple rows froma single function application. This is also called a "set-returningfunction".

For example:

> SELECT * FROM generate_series(1, 3);

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

Set-returning functions (SRFs) can now be accessed 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 (note that the output of queries against information_schema 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)

Note:

Currently CockroachDB only supports a small set of generator functions compatible with the PostgreSQL set-generating functions with the samenames.

Operators that extend a table expression

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

Aliased table expressions

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

Syntax:

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

In the first form, the table expression is equivalent to its left operandwith 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

Syntax:

<table expr> WITH ORDINALITY

Designates a data source equivalent to the table expression operand withan extra "Ordinality" column that enumerates every row in the data source.

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

Note:

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

Join expressions

Join expressions combine the results of two or more table expressionsbased on conditions on the values of particular columns.

See Join Expressions for more details.

Using other queries as table expressions

The following sections describe how to use the results produced byanother SQL query or statement as a table expression.

Subqueries as table expressions

Any selectionquery enclosedbetween parentheses can be used as a table expression, includingsimple SELECT clauses. This is called a"subquery".

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

Note:

• See also Subqueries for more details and performance best practices.
• To use other statements that produce data in a table expression, for example SHOW, use the square bracket notation.

Using the output of other statements

Syntax:

[ <statement> ]

An explainable statementbetween square brackets in a table expression context designates theoutput of executing said statement.

Note:

This is a CockroachDB extension. This syntax complements the subquery syntax using parentheses, which is restricted to selection queries. It was introduced to enable use of any explainable statement as subquery, including SHOW and other non-query statements.

For example:

> SELECT "column_name" FROM [SHOW COLUMNS FROM customer];

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

The following statement inserts Albert in the employee table andimmediately creates a matching entry in the management table with theauto-generated employee ID, without requiring a round trip with the SQLclient:

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

Composability

Table expressions are used in the SELECT andTABLE variants of selectionclauses, and thus can appear everywhere wherea selection clause is possible. For example:

> SELECT ... FROM <table expr>, <table expr>, ...
> TABLE <table expr>
> INSERT INTO ... SELECT ... FROM <table expr>, <table expr>, ...
> INSERT INTO ... TABLE <table expr>
> CREATE TABLE ... AS SELECT ... FROM <table expr>, <table expr>, ...
> UPSERT INTO ... SELECT ... FROM <table expr>, <table expr>, ...

For more options to compose query results, see Selection Queries.

See also

• Constants
• Selection Queries
  • Selection Clauses
• Explainable Statements
• Scalar Expressions
• Data Types
• Subqueries

Was this page helpful?
YesNo