> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Table Expressions

A *table expression* defines a data source in the `FROM` sub-clause of a [`SELECT` clause](https://docs.cockroachlabs.com/docs/v24.2/select-clause) or as parameter to a [`TABLE` clause](https://docs.cockroachlabs.com/docs/v24.2/selection-queries#table-clause).

A [join](https://docs.cockroachlabs.com/docs/v24.2/joins) is a particular kind of table expression.

## Synopsis

![table\_ref syntax diagram](https://mintcdn.com/cockroachlabs/M6E8QStE4G5ISd4O/images/sql-diagrams/common/table_ref.svg?fit=max\&auto=format\&n=M6E8QStE4G5ISd4O\&q=85\&s=16eff68b9f5647da12c8fa4c0c24494f)

## 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](https://docs.cockroachlabs.com/docs/v24.2/sql-grammar). |
| `select_stmt` | A [selection query](https://docs.cockroachlabs.com/docs/v24.2/selection-queries) to use as [subquery](#use-a-subquery). |
| `joined_table` | A [join expression](https://docs.cockroachlabs.com/docs/v24.2/joins). |

## 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](https://docs.cockroachlabs.com/docs/v24.2/joins). | `orders o JOIN customers c ON o.customer_id = c.id` |
| `(... subquery ...)` | A [selection query](https://docs.cockroachlabs.com/docs/v24.2/selection-queries) used as [subquery](#use-a-subquery). | `(SELECT * FROM customers c)` |
| `[... statement ...]` | Use the result rows of an [explainable statement](https://docs.cockroachlabs.com/docs/v24.2/sql-grammar). This is a CockroachDB extension. However, Cockroach Labs recommends that you use the standard SQL [CTE syntax](https://docs.cockroachlabs.com/docs/v24.2/common-table-expressions) 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](https://docs.cockroachlabs.com/docs/v24.2/views), or sequence with that name
in the current database, as configured by [`SET DATABASE`](https://docs.cockroachlabs.com/docs/v24.2/set-vars).

If the name is composed of two or more identifiers, [name resolution](https://docs.cockroachlabs.com/docs/v24.2/sql-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](https://docs.cockroachlabs.com/docs/v24.2/indexes) when reading from a named table.

> [!NOTE]
>
> Index selection can impact [performance](https://docs.cockroachlabs.com/docs/v24.2/performance-best-practices-overview), but does not change the result of a query.

##### 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](https://docs.cockroachlabs.com/docs/v24.2/performance-best-practices-overview). 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](https://docs.cockroachlabs.com/docs/v24.2/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)`](https://docs.cockroachlabs.com/docs/v24.2/explain#opt-option). 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](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes) 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](https://docs.cockroachlabs.com/docs/v24.2/partial-indexes), 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](https://docs.cockroachlabs.com/docs/v24.2/inverted-indexes#partial-gin-indexes) 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](https://docs.cockroachlabs.com/docs/v24.2/partial-indexes) for a specific table, you must specify `NO_FULL_SCAN` in combination with the index name using [`FORCE_INDEX`](https://docs.cockroachlabs.com/docs/v24.2/table-expressions#force-index-selection). 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.

> [!TIP]
>
> For other ways to prevent full scans, refer to [Prevent the optimizer from planning full scans](https://docs.cockroachlabs.com/docs/v24.2/performance-best-practices-overview#prevent-the-optimizer-from-planning-full-scans).

> [!NOTE]
>
> You can also force index selection for [`DELETE`](https://docs.cockroachlabs.com/docs/v24.2/delete#force-index-selection-for-deletes) and [`UPDATE`](https://docs.cockroachlabs.com/docs/v24.2/update#force-index-selection-for-updates) statements.

### Access a common table expression

A single identifier in a table expression can refer to a
[common table expression](https://docs.cockroachlabs.com/docs/v24.2/common-table-expressions) 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](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/sql-name-resolution) for more details.

#### Scalar function as data source

When a [function returning a single value](https://docs.cockroachlabs.com/docs/v24.2/scalar-expressions#function-calls-and-sql-special-forms) 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`](https://docs.cockroachlabs.com/docs/v24.2/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)
```

> [!NOTE]
>
> 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 <name>
 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:

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

> [!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`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/joins) 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](https://docs.cockroachlabs.com/docs/v24.2/selection-queries) enclosed between parentheses `()`
as a table expression. This is called a *[subquery](https://docs.cockroachlabs.com/docs/v24.2/subqueries)*.

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

> [!NOTE]
>
> * See [Subqueries](https://docs.cockroachlabs.com/docs/v24.2/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 ( <stmt> ) SELECT .. FROM table_expr
```

A [`WITH` query](https://docs.cockroachlabs.com/docs/v24.2/common-table-expressions) designates the output of executing a statement as a row source. The following statements are supported as row sources for table expressions:

* [`DELETE`](https://docs.cockroachlabs.com/docs/v24.2/delete)
* [`EXPLAIN`](https://docs.cockroachlabs.com/docs/v24.2/explain)
* [`INSERT`](https://docs.cockroachlabs.com/docs/v24.2/insert)
* [`SELECT`](https://docs.cockroachlabs.com/docs/v24.2/select-clause)
* [`SHOW`](https://docs.cockroachlabs.com/docs/v24.2/sql-statements#data-definition-statements)
* [`UPDATE`](https://docs.cockroachlabs.com/docs/v24.2/update)
* [`UPSERT`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/select-clause) and
[`TABLE` clause](https://docs.cockroachlabs.com/docs/v24.2/selection-queries#table-clause) variants of [selection clauses](https://docs.cockroachlabs.com/docs/v24.2/selection-queries#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](https://docs.cockroachlabs.com/docs/v24.2/selection-queries).

## See also

* [Constants](https://docs.cockroachlabs.com/docs/v24.2/sql-constants)
* [Explainable statements](https://docs.cockroachlabs.com/docs/v24.2/sql-grammar)
* [Scalar Expressions](https://docs.cockroachlabs.com/docs/v24.2/scalar-expressions)
* [Data Types](https://docs.cockroachlabs.com/docs/v24.2/data-types)
* [Subqueries](https://docs.cockroachlabs.com/docs/v24.2/subqueries)