> ## 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.
# CREATE VIEW
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}
;
};
The `CREATE VIEW` statement creates a new view, which is a stored query represented as a virtual table.
By default, views created in a database cannot reference objects in a different database. To enable cross-database references for views, set the `sql.cross_db_views.enabled` cluster setting to `true`.
The `CREATE VIEW` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
## Required privileges
The user must have the `CREATE` privilege on the parent database and the `SELECT` privilege on any table(s) referenced by the view.
## Synopsis
## Parameters
| Parameter | Description |
| ----------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `MATERIALIZED` | Create a materialized view. |
| `IF NOT EXISTS` | Create a new view only if a view of the same name does not already exist. If one does exist, do not return an error. Note that `IF NOT EXISTS` checks the view name only. It does not check if an existing view has the same columns as the new view. |
| `OR REPLACE` | Create a new view if a view of the same name does not already exist. If a view of the same name already exists, replace that view. In order to replace an existing view, the new view must have the same columns as the existing view, or more. If the new view has additional columns, the old columns must be a prefix of the new columns. For example, if the existing view has columns `a, b`, the new view can have an additional column `c`, but must have columns `a, b` as a prefix. In this case, `CREATE OR REPLACE VIEW myview (a, b, c)` would be allowed, but `CREATE OR REPLACE VIEW myview (b, a, c)` would not. |
| `view\_name` | The name of the view to create, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as `database.name`. |
| `name\_list` | An optional, comma-separated list of column names for the view. If specified, these names will be used in the response instead of the columns specified in `AS select\_stmt`. |
| `AS select\_stmt` | The selection query to execute when the view is requested. Note that it is not currently possible to use `\*` to select all columns from a referenced table or view; instead, you must specify specific columns. |
| `opt\_temp` | Defines the view as a session-scoped temporary view. For more information, see Temporary Views. **Support for temporary views is in preview**. |
## Example
This example highlights one key benefit to using views: simplifying complex queries. For additional benefits and examples, see Views.
### Setup
The following examples use the `startrek` demo database schema.
To follow along, run `cockroach demo startrek` to start a temporary, in-memory cluster with the `startrek` schema and dataset preloaded:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo startrek
```
### Create a view
The sample `startrek` database contains two tables, `episodes` and `quotes`. The table also contains a foreign key constraint, between the `episodes.id` column and the `quotes.episode` column. To count the number of famous quotes per season, you could run the following join:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT startrek.episodes.season, count(*)
FROM startrek.quotes
JOIN startrek.episodes
ON startrek.quotes.episode = startrek.episodes.id
GROUP BY startrek.episodes.season;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
season | count
---------+--------
1 | 78
2 | 76
3 | 46
(3 rows)
```
Alternatively, to make it much easier to run this complex query, you could create a view:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE VIEW startrek.quotes_per_season (season, quotes)
AS SELECT startrek.episodes.season, count(*)
FROM startrek.quotes
JOIN startrek.episodes
ON startrek.quotes.episode = startrek.episodes.id
GROUP BY startrek.episodes.season;
```
The view is then represented as a virtual table alongside other tables in the database:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM startrek;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
schema_name | table_name | type | estimated_row_count
--------------+-------------------+-------+----------------------
public | episodes | table | 79
public | quotes | table | 200
public | quotes_per_season | view | 3
(3 rows)
```
Executing the query is as easy as `SELECT`ing from the view, as you would from a standard table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM startrek.quotes_per_season;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
season | quotes
---------+---------
3 | 46
1 | 78
2 | 76
(3 rows)
```
### Replace an existing view
You can create a new view, or replace an existing view, with `CREATE OR REPLACE VIEW`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE OR REPLACE VIEW startrek.quotes_per_season (season, quotes)
AS SELECT startrek.episodes.season, count(*)
FROM startrek.quotes
JOIN startrek.episodes
ON startrek.quotes.episode = startrek.episodes.id
GROUP BY startrek.episodes.season
ORDER BY startrek.episodes.season;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM startrek.quotes_per_season;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
season | quotes
---------+---------
3 | 46
1 | 78
2 | 76
(3 rows)
```
## See also
* Selection Queries
* Views
* `SHOW CREATE`
* `ALTER VIEW`
* `DROP VIEW`
* Online Schema Changes