> ## 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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

A *table expression* defines a data source in the `FROM` sub-clause of a <InternalLink path="select-clause">`SELECT` clause</InternalLink> or as parameter to a <InternalLink path="selection-queries#table-clause">`TABLE` clause</InternalLink>.

A <InternalLink path="joins">join</InternalLink> is a particular kind of table expression.

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/9gyQKEP-CuQuCsI3/images/sql-diagrams/v25.2/table_ref.svg?fit=max&auto=format&n=9gyQKEP-CuQuCsI3&q=85&s=9907f794d59e0b85979f82930a2d5f27" alt="table_ref syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="1211" height="505" data-path="images/sql-diagrams/v25.2/table_ref.svg" />

## 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 <InternalLink path="sql-grammar">supported statement</InternalLink>. |
| `select\_stmt`                 | A <InternalLink path="selection-queries">selection query</InternalLink> to use as [subquery](#use-a-subquery).                  |
| `joined\_table`                | A <InternalLink path="joins">join expression</InternalLink>.                                                                    |

## 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...`                      | <InternalLink path="joins">Join expression</InternalLink>.                                                                                                                                                                                                                                                                                                                          | `orders o JOIN customers c ON o.customer\_id = c.id`  |
| `(... subquery...)`                | A <InternalLink path="selection-queries">selection query</InternalLink> used as [subquery](#use-a-subquery).                                                                                                                                                                                                                                                                        | `(SELECT \* FROM customers c)`                        |
| `[... statement...]`               | Use the result rows of an <InternalLink path="sql-grammar">explainable statement</InternalLink>. This is a CockroachDB extension. However, Cockroach Labs recommends that you use the standard SQL <InternalLink path="common-table-expressions">CTE syntax</InternalLink> 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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
identifier
identifier.identifier
identifier.identifier.identifier
```

A single SQL identifier in a table expression designates the contents of the table, <InternalLink path="views">view</InternalLink>, or sequence with that name in the current database, as configured by <InternalLink path="set-vars">`SET DATABASE`</InternalLink>.

If the name is composed of two or more identifiers, <InternalLink path="sql-name-resolution">name resolution</InternalLink> 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 <InternalLink path="indexes">index</InternalLink> when reading from a named table.

<Note>
  Index selection can impact <InternalLink path="performance-best-practices-overview">performance</InternalLink>, but does not change the
  result of a query.
</Note>

##### 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 <InternalLink path="performance-best-practices-overview">performance tuning</InternalLink>. 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 <InternalLink path="cost-based-optimizer">cost-based optimizer</InternalLink> 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 <InternalLink path="explain#opt-option">`EXPLAIN (OPT)`</InternalLink>. 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 theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                 text
+-------------------------------------+
  scan users,rev
   └── flags: force-index=primary,rev
(2 rows)
```

#### Force inverted index scan

To force a scan of any <InternalLink path="inverted-indexes">inverted index</InternalLink> 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:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ERROR: could not produce a query plan conforming to the FORCE_INVERTED_INDEX hint
```

##### Force partial index scan

To force a <InternalLink path="partial-indexes">partial index scan</InternalLink>, 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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="inverted-indexes#partial-gin-indexes">partial GIN index</InternalLink> 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';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="partial-indexes">partial index</InternalLink> for a specific table, you must specify `NO_FULL_SCAN` in combination with the index name using <InternalLink path="table-expressions#force-index-selection">`FORCE_INDEX`</InternalLink>. 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 <InternalLink path="performance-best-practices-overview#prevent-the-optimizer-from-planning-full-scans">Prevent the optimizer from planning full
  scans</InternalLink>.

<Note>
  You can also force index selection for <InternalLink path="delete#force-index-selection-for-deletes">`DELETE`</InternalLink> and
  <InternalLink path="update#force-index-selection-for-updates">`UPDATE`</InternalLink> statements.
</Note>

### Access a common table expression

A single identifier in a table expression can refer to a <InternalLink path="common-table-expressions">common table expression</InternalLink> 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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
name ( arguments... )
```

The name of a function, followed by an opening parenthesis, followed by zero or more <InternalLink path="scalar-expressions">scalar expressions</InternalLink>, followed by a closing parenthesis.

The resolution of the function name follows the same rules as the resolution of table names. See <InternalLink path="sql-name-resolution">Name Resolution</InternalLink> for more details.

#### Scalar function as data source

When a <InternalLink path="scalar-expressions#function-calls-and-sql-special-forms">function returning a single value</InternalLink> 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)
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-----------------------+
|          sin          |
+-----------------------+
| -0.058374143427580086 |
+-----------------------+
```

<Note>
  CockroachDB supports this syntax for compatibility with PostgreSQL. The canonical syntax to evaluate <InternalLink path="scalar-expressions">scalar
  functions</InternalLink> is as a direct target of `SELECT`, for example `SELECT sin(3.2)`.
</Note>

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-----------------+
| 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 <InternalLink path="information-schema">`information_schema`</InternalLink> 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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
 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).

## 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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
 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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
 WITH ORDINALITY
```

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM (VALUES('a'),('b'),('c'));
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+---------+
| column1 |
+---------+
| a       |
| b       |
| c       |
+---------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM (VALUES ('a'), ('b'), ('c')) WITH ORDINALITY;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+---------+------------+
| 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 <InternalLink path="explain">`EXPLAIN`</InternalLink> in case of doubt.
</Note>

## `JOIN` expressions

`JOIN` expressions combine the results of two or more table expressions based on conditions on the values of particular columns.

See <InternalLink path="joins">`JOIN` expressions</InternalLink> 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 <InternalLink path="selection-queries">selection query</InternalLink> enclosed between parentheses `()` as a table expression. This is called a *<InternalLink path="subqueries">subquery</InternalLink>*.

#### Syntax

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
( ... 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 <InternalLink path="subqueries">Subqueries</InternalLink> 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).
</Note>

### Use the output of another statement

#### Syntax

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH table_expr AS ( <stmt> ) SELECT .. FROM table_expr
```

A <InternalLink path="common-table-expressions">`WITH` query</InternalLink> designates the output of executing a statement as a row source. The following statements are supported as row sources for table expressions:

* <InternalLink path="delete">`DELETE`</InternalLink>
* <InternalLink path="explain">`EXPLAIN`</InternalLink>
* <InternalLink path="insert">`INSERT`</InternalLink>
* <InternalLink path="select-clause">`SELECT`</InternalLink>
* <InternalLink path="sql-statements#data-definition-statements">`SHOW`</InternalLink>
* <InternalLink path="update">`UPDATE`</InternalLink>
* <InternalLink path="upsert">`UPSERT`</InternalLink>

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> WITH x AS (SHOW COLUMNS from customer) SELECT "column_name" FROM x;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-------------+
| 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 <InternalLink path="select-clause">`SELECT` clause</InternalLink> and <InternalLink path="selection-queries#table-clause">`TABLE` clause</InternalLink> variants of <InternalLink path="selection-queries#selection-clauses">selection clauses</InternalLink>. 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 <InternalLink path="selection-queries">Selection Queries</InternalLink>.

## See also

* <InternalLink path="sql-constants">Constants</InternalLink>
* <InternalLink path="sql-grammar">Explainable statements</InternalLink>
* <InternalLink path="scalar-expressions">Scalar Expressions</InternalLink>
* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="subqueries">Subqueries</InternalLink>