Table expressions define a data source in the FROM sub-clause of
simple SELECT clauses, or as parameter to
TABLE.
SQL Joins are 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 |
Results from a function. |
preparable_stmt |
Use the result rows of a preparable statement. |
select_stmt |
A selection query to use as subquery. |
joined_table |
A join expression. |
Table expressions language
The synopsis above really 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) |
<table expr> [AS] name [( name [, ...] )] |
Rename a table and optionally columns. | accounts a, accounts AS a, accounts AS a(id, b) |
<table expr> WITH ORDINALITY |
Enumerate the result rows. | accounts WITH ORDINALITY |
<table expr> JOIN <table expr> ON ... |
Join expression. | orders o JOIN customers c ON o.customer_id = c.id |
(... subquery ...) |
A selection query used as subquery. | (SELECT * FROM customers c) |
[... statement ...] |
Use the result rows of an explainable statement. This is a CockroachDB extension. |
[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.
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 designates
the contents of the table, view, or sequence with that name
in 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.
> SHOW INDEXES FROM accounts;
+----------+-------------------+--------+-----+--------+-----------+---------+----------+
| Table | Name | Unique | Seq | Column | Direction | Storing | Implicit |
+----------+-------------------+--------+-----+--------+-----------+---------+----------+
| accounts | primary | true | 1 | id | ASC | false | false |
| accounts | accounts_name_idx | false | 1 | name | ASC | false | false |
| accounts | accounts_name_idx | false | 2 | id | ASC | false | true |
+----------+-------------------+--------+-----+--------+-----------+---------+----------+
(3 rows)
> SELECT name, balance
FROM accounts@accounts_name_idx
WHERE name = 'Edna Barath';
+-------------+---------+
| name | balance |
+-------------+---------+
| Edna Barath | 750 |
| Edna Barath | 2200 |
+-------------+---------+
Access a common table expression
A single identifier in a table expression context can refer to a common table expression defined earlier.
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 as a data source.
Syntax:
name ( arguments... )
The name of a function, followed by an opening parenthesis, followed by zero or more scalar expressions, followed by a closing parenthesis.
The resolution of the function name follows the same rules as the resolution of table names. See Name Resolution for more details.
Scalar function as data source
When a function returning a single value is used as a table expression, it is interpreted as tabular data with a single column and single row containing the function results.
For example:
> SELECT * FROM sin(3.2)
+-----------------------+
| sin |
+-----------------------+
| -0.058374143427580086 |
+-----------------------+
SELECT, for example SELECT sin(3.2).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".
For example:
> SELECT * FROM generate_series(1, 3);
+-----------------+
| generate_series |
+-----------------+
| 1 |
| 2 |
| 3 |
+-----------------+
New in v2.1: 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)
Currently CockroachDB only supports a small set of 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:
<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);
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.
Join expressions
Join expressions combine the results of two or more table expressions based 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 by another SQL query or statement as a table expression.
Subqueries as table expressions
Any selection
query enclosed
between parentheses can be used as a table expression, including
simple 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);
- 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 statement between square brackets in a table expression context designates the output of executing said statement.
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 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]));
Composability
Table expressions are used in the SELECT and
TABLE variants of selection
clauses, and thus can appear everywhere where
a 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
Was this helpful?
