Table Expressions

On this page Carat arrow pointing down
Warning:
As of November 10, 2018, CockroachDB v1.0 is no longer supported. For more details, refer to the Release Support Policy.

Table expressions define a data source in the FROM clause of SELECT and INSERT statements.

Introduction

Table expressions are used prominently in the SELECT clause:

> SELECT ... FROM <table expr>, <table expr>, ...
> INSERT INTO ... SELECT ... FROM <table expr>, <table expr>, ...

CockroachDB recognizes the following table expressions:

The following sections provide details on each of these options.

In addition to this, the FROM clause itself accepts more than one consecutive table expressions at the top level, separated by commas. This is a shorthand notation for CROSS JOIN, documented in the JOIN syntax below.

Table or View Names

Syntax:

identifier
identifier.identifier

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

If the name is prefixed by another identifier and a period, the table or view is searched in the database with that name. See the section on name resolution for more details.

For example:

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

Table Generator Functions

Syntax:

name ( arguments... )

The name of a table generator function, followed by an opening parenthesis, followed by zero or more expression arguments, followed by a closing parenthesis.

This designates a transient data source produced by the designated function.

Currently CockroachDB only supports the generator function pg_catalog.generate_series(), for compatibility with the PostgreSQL set-generating function of the same name.

For example:

> SELECT * FROM generate_series(1, 3)
+-----------------+
| generate_series |
+-----------------+
|               1 |
|               2 |
|               3 |
+-----------------+

Subqueries as Table Expressions

Syntax:

( ... subquery ... )

The subquery can be expressed either as a SELECT or VALUES clause. The parentheses around the subquery are mandatory.

For example:

> SELECT * FROM (VALUES(1), (2), (3));
> SELECT c+2 FROM (SELECT COUNT(*) AS c FROM users);

Aliased Table Expressions

Syntax:

<table expr> AS <name>
<table expr> 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);

Join Expressions

Syntax:

# Inner joins:
<table expr> [ INNER ] JOIN <table expr> ON <val expr>
<table expr> [ INNER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL [ INNER ] JOIN <table expr>
<table expr> CROSS JOIN <table expr>

# Left outer joins:
<table expr> LEFT [ OUTER ] JOIN <table expr> ON <val expr>
<table expr> LEFT [ OUTER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL LEFT [ OUTER ] JOIN <table expr>

# Right outer joins:
<table expr> RIGHT [ OUTER ] JOIN <table expr> ON <val expr>
<table expr> RIGHT [ OUTER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL RIGHT [ OUTER ] JOIN <table expr>

These expressions designate the SQL join operation on the two operand table expressions.

Currently works only with small data sets; find more info in our blog post.

Ordinality Annotation

Syntax:

<table expr> WITH ORDINALITY

Designates a data source equivalent to the table expression operand with an 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.

See Also


Yes No
On this page

Yes No