> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Table Expressions
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
A *table expression* defines a data source in the `FROM` sub-clause of a `SELECT` clause or as parameter to a `TABLE` clause.
A join is a particular kind of table expression.
## Synopsis
## Parameters
| Parameter | Description |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| `table_name` | A [table or view name](#table-and-view-names). |
| `table_alias_name` | A name to use in an [aliased table expression](#aliased-table-expressions). |
| `name` | One or more aliases for the column names, to use in an [aliased table expression](#aliased-table-expressions). |
| `index_name` | Optional syntax to [force index selection](#force-index-selection). |
| `func_application` | [Result from a function](#result-from-a-function). |
| `row_source_extension_stmt` | [Result rows](#use-the-output-of-another-statement) from a supported statement. |
| `select_stmt` | A selection query to use as [subquery](#use-a-subquery). |
| `joined_table` | A join expression. |
## 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](#table-and-view-names). | `accounts`, `accounts@name_idx` |
| `function_name ( exprs ... )` | Generate tabular data using a [scalar function](#scalar-function-as-data-source) or [table generator function](#table-generator-functions). | `sin(1.2)`, `generate_series(1,10)` |
| ` [AS] name [( name [, ...] )]` | [Rename a table and optionally columns](#aliased-table-expressions). | `accounts a`, `accounts AS a`, `accounts AS a(id, b)` |
| ` WITH ORDINALITY` | [Enumerate the result rows](#ordinality-annotation). | `accounts WITH ORDINALITY` |
| ` JOIN ON ...` | Join expression. | `orders o JOIN customers c ON o.customer_id = c.id` |
| `(... subquery ...)` | A selection query used as [subquery](#use-a-subquery). | `(SELECT * FROM customers c)` |
| `[... statement ...]` | Use the result rows of an explainable statement.
This is a CockroachDB extension. However, Cockroach Labs recommends that you use the standard SQL CTE syntax instead. See [Use the output of another statement](#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, 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM users; -- uses table `users` in the current database
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](https://www.cockroachlabs.com/blog/index-selection-cockroachdb-2/) and use a specific index when reading from a named table. This is called an *index hint*.
Index selection can impact performance, but does not change the result of a query.
You can rewrite inline hints to apply index hints without modifying the original query text.
##### Force index scan
To force a scan of a specific index:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM table@my_idx;
```
This is equivalent to the longer expression:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM table@{FORCE_INDEX=my_idx};
```
##### Force reverse scan
To force a reverse scan of a specific index:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM table@{FORCE_INDEX=my_idx,DESC};
```
Forcing a reverse scan can help with performance tuning. To choose an index and its scan direction:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE kv (K INT PRIMARY KEY, v INT);
```
you can check the scan direction with:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
EXPLAIN (opt) SELECT * FROM users@{FORCE_INDEX=primary,DESC};
```
```
text
+-------------------------------------+
scan users,rev
└── flags: force-index=primary,rev
(2 rows)
```
#### Force inverted index scan
To force a scan of any inverted index of the hinted table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM table@{FORCE_INVERTED_INDEX};
```
The `FORCE_INVERTED_INDEX` hint does not allow specifying an inverted index. If no query plan can be generated, the query will result in the error:
```
ERROR: could not produce a query plan conforming to the FORCE_INVERTED_INDEX hint
```
##### Force partial index scan
To force a partial index scan, your statement must have a `WHERE` clause that implies the partial index filter.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 partial GIN index scan, your statement must have a `WHERE` clause that:
* Implies the partial index.
* Constrains the GIN index scan.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 specific table, specify the `NO_FULL_SCAN` index hint. For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM table_name@{NO_FULL_SCAN};
```
* To prevent a full scan of a partial index for a specific table, you must specify `NO_FULL_SCAN` in combination with the index name using `FORCE_INDEX`. For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM table_name@{FORCE_INDEX=index_name,NO_FULL_SCAN} WHERE b > 0;
```
This forces a constrained scan of the partial index. If a constrained scan of the partial index is not possible, an error is returned.
For other ways to prevent full scans, refer to Prevent the optimizer from planning full scans.
You can also force index selection for `DELETE` and `UPDATE` statements.
### Access a common table expression
A single identifier in a table expression can refer to a
common table expression defined
earlier.
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 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 result.
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 `information_schema` will vary per database):
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](https://www.postgresql.org/docs/9.6/static/functions-srf.html).
## 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
AS (, ...)
```
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT c.x FROM (SELECT COUNT(*) AS x FROM users) AS c;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM (VALUES('a'),('b'),('c'));
```
```
+---------+
| column1 |
+---------+
| a |
| b |
| c |
+---------+
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.
## 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 selection query enclosed between parentheses `()`
as a table expression. This is called a *subquery*.
#### Syntax
```
( ... subquery ... )
```
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT c+2 FROM (SELECT COUNT(*) AS c FROM users);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM (VALUES(1), (2), (3));
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT firstname || ' ' || lastname FROM (TABLE employees);
```
* See Subqueries 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).
### Use the output of another statement
#### Syntax
```
WITH table_expr AS ( ) SELECT .. FROM table_expr
```
A `WITH` query designates the output of executing a statement as a row source. The following statements are supported as row sources for table expressions:
* `DELETE`
* `EXPLAIN`
* `INSERT`
* `SELECT`
* `SHOW`
* `UPDATE`
* `UPSERT`
For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 `SELECT` clause and
`TABLE` clause variants of selection clauses.
Thus they can appear everywhere where a selection clause is possible. For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 Selection Queries.
## See also
* Constants
* Explainable statements
* Scalar Expressions
* Data Types
* Subqueries