Table expressions define a data source in the FROM sub-clause of
selection clauses, allowed also in the CREATE
TABLE AS, INSERT and
UPSERT statements.
Introduction
Table expressions are used in selection clauses:
> SELECT ... FROM <table expr>, <table expr>, ...
> INSERT INTO ... SELECT ... FROM <table expr>, <table expr>, ...
> CREATE TABLE ... AS SELECT ... FROM <table expr>, <table expr>, ...
> UPSERT INTO ... SELECT ... FROM <table expr>, <table expr>, ...
CockroachDB recognizes the following table expressions:
- a table or view name;
- a table generator function;
- a selection clause between parentheses (including
SELECT,VALUESandTABLE), as a sub-query; - an aliased table expression, using an
ASclause; - an explicit
JOINexpression; - a CockroachDB statement that returns values, between square brackets '[...]';
- another table expression annoted with
WITH ORDINALITY; or - another table expression between parentheses.
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
Any selection clause enclosed between parentheses can be used as a table expression. 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);
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.
Using the Output of Other Statements
Syntax:
[ <statement> ]
A statement between square brackets in a table expression context designates the output of executing said statement. The following statements produce values that can be used in this way:
- All
SHOWvariants. INSERT,DELETE,UPDATEandDELETEwithRETURNING.EXPLAIN.- All selection clauses. However the fact they can be used between square brackets is merely a convenience; it is more common to use them enclosed in parentheses, as outlined in the next section.
For example:
> SELECT "Field" FROM [SHOW COLUMNS FROM customer];
+---------+
| Field |
+---------+
| id |
| name |
| address |
+---------+
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'),
(SELECT id FROM [INSERT INTO employee(name) VALUES ('Albert') RETURNING id]));
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 |
+---------+------------+
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
Was this helpful?
