# CREATE VIEW

The `CREATE VIEW` command creates an unmaterialized view.

SingleStore supports both writable and non-writable views. By default, views in SingleStore are non-writable. Attempts to run `INSERT`, `UPDATE`, or `DELETE` queries on a non-writable view result in an error. Instead, use the `ALTER VIEW` command or update data directly in the underlying tables from which the view is created.

Writable views support `INSERT`, `UPDATE`, and `DELETE` queries. Refer to [Writable Views](https://docs.singlestore.com/#section-idm234617498191001.md) for more information.

## Syntax

```sql
CREATE
    [DEFINER = { <user> | CURRENT_USER }]
    [SCHEMA_BINDING = { ON | OFF }]
    VIEW [IF NOT EXISTS] <view_name>
    AS <select_statement>

```

## Writable Views

Inserting, updating, or deleting rows in a writable view also affects the rows in the underlying table. Writable views can act as targets for data ingestion through pipelines. When creating a pipeline with `CREATE PIPELINE`, specify a writable view in the `INTO TABLE` clause to allow the pipeline to insert data into the underlying table through the view.

## Enable Writable Views

To enable creation of writable views, set the `enable_writable_views` global variable to `1`.

```sql
SET GLOBAL enable_writable_views = 1;
```

Not all views are writable. After creating a view, query the `information_schema.VIEWS` information schema view to inspect if a view can be updated. For example:

```sql
SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
WHERE TABLE_NAME='shipped_products';

```

```output

+------------------+--------------+
| TABLE_NAME       | IS_UPDATABLE |
+------------------+--------------+
| shipped_products | YES          |
+------------------+--------------+
```

Whether a view supports `INSERT` and `DELETE` queries depends on the view definition. Refer to [Writable View Definition Limitations](https://docs.singlestore.com/#section-idm234617522129172.md) for more information.

> **📝 Note**: Information schema views are not writable.

## Writable View Definition Limitations

To create a view that can be updated, the view definition must *not* contain the following:

* Aggregate functions or window functions
* `DISTINCT`, `UNION`, `UNION ALL`, `EXCEPT`, or `INTERSECT` clause
* `GROUP BY` , `HAVING`, `JOIN`, or `LIMIT` clause
* References to non-table entities, such as a view or derived table, in the `FROM` clause
* Complex expression or derived (non-simple) column references in the `SELECT` subquery

To support `INSERT` queries, the view definition must conform to the restrictions specified earlier as well as the following:

* The view definition must *not* contain multiple references to the same column from the underlying table.
* The view definition *must* contain all the columns from the underlying table that do not have a default value.

## Permissions Required

The user that defines the view (view definer) must have the `INSERT`, `UPDATE`, or `DELETE` permissions (or privilege) to the underlying table to perform the respective operations on the view. Additionally, the view definer needs the `INSERT`, `UPDATE`, or `DELETE` permissions to grant the respective permission on the view to other users. However, other users only need the write (`INSERT`, `UPDATE`, or `DELETE`) privilege for the view to perform the respective write operations on the view.

The following permissions apply to writable views: `INSERT`, `UPDATE`, and `DELETE`. For example:

```sql
GRANT INSERT, UPDATE, DELETE ON <view_name> TO <user>;
```

If the view definer's `INSERT`, `UPDATE`, or `DELETE` permission to the underlying table is revoked (for example by admin users), then the respective `INSERT`, `UPDATE`, or `DELETE` privilege is revoked for other users as well.

## Remarks

The following apply to both writable and non-writable views.

* **Permissions**:

  * The user who created the view is granted the ability to `CREATE`, `ALTER`, and `SELECT` from the view.
  * The `SELECT` permission on the view is required to query it.
  * The following permissions apply to views: `SELECT`, `CREATE VIEW`, `ALTER VIEW`, `DROP VIEW`, `SHOW VIEW`

  Refer to the [permissions matrix](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/permissions-matrix.md) for details.
* SingleStore Helios does not support MySQL’s `CREATE OR REPLACE VIEW` syntax. To modify an existing view, use `ALTER VIEW`.
* By default, SingleStore Helios restricts the ability to `CREATE`, `ALTER`, or `SELECT` from the view to the user who created the view. In MySQL terms, this is the equivalent of including `DEFINER = CURRENT_USER` and `SQL SECURITY DEFINER` in the `CREATE VIEW` statement.
* Views can reference views or tables from other databases.
* `CREATE VIEW view_name AS SELECT * FROM table_name ...` will replace `*` with the column names at the time of `CREATE VIEW` execution rather than during execution of `SELECT ... FROM view_name`. It is important to consider this distinction if `table_name` is altered between the execution of `CREATE VIEW` and `SELECT ... FROM view_name`.
* Any function (including `CURRENT_USER()` and `NOW()`) used in a view are evaluated during execution of `SELECT ... FROM view_name`, not when the view is created.
* The `DEFINER` clause tells SingleStore which user to use for security checks when a view is referenced by a query. The default value is CURRENT\_USER.
* When `SCHEMA_BINDING` is set to `ON`, objects referenced by the view cannot be dropped if the view exists; the view must be dropped before dropping these objects. By default, `SCHEMA_BINDING` is set to `OFF`.
* The following note applies when the engine variable `table_name_case_sensitivity` is set to `OFF`: After you create a view, you cannot create another view having the same view name with a different case. Refer to [Database Object Case-Sensitivity](https://docs.singlestore.com/cloud/reference/sql-reference/database-object-case-sensitivity.md) for more information.
* This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## Examples

The following examples create views that reference a table in the same database as the view.

```sql
CREATE VIEW person_view AS SELECT first_name, last_name
FROM table_name WHERE user_id = 'real_person';

```

```sql
CREATE VIEW active_items_view AS SELECT name
FROM items WHERE status = 'active';

```

```sql
CREATE VIEW discounted_items_view AS SELECT name
FROM active_items_view WHERE discount = 1;

```

The following example creates a view that references two tables (`orders` and `customers`) that are located in different databases (`orders_db` and `customers_db`).

```sql
CREATE VIEW customer_orders AS SELECT o.id, c.last_name, c.first_name
FROM orders_db.orders o, customers_db.customers c WHERE o.customer_id = c.id;

```

## Create a Writable View

This example creates a writable view using the following `orders` table and then performs insert and delete operations on the view:

```sql
CREATE TABLE orders (
    product_id INT,
    quantity INT
);

INSERT INTO orders (product_id, quantity) VALUES
(101, 5),
(102, 3),
(101, 2),
(103, 4),
(102, 6),
(101, 3),
(104, 7),
(103, 2),
(102, 4),
(101, NULL);
```

1. Enable writable views:
   ```sql
   SET GLOBAL enable_writable_views = 1;
   ```

2. Create a view:
   ```sql
   CREATE VIEW shipped_products AS
   SELECT product_id, quantity FROM orders
   WHERE quantity IS NOT NULL;
   ```

3. Verify if the view is writable:
   ```sql
   SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
   WHERE TABLE_NAME='shipped_products';

   ```
   ```output

   +------------------+--------------+
   | TABLE_NAME       | IS_UPDATABLE |
   +------------------+--------------+
   | shipped_products | YES          |
   +------------------+--------------+
   ```

4. Query the view:
   ```sql
   SELECT product_id, SUM(quantity) FROM shipped_products 
   GROUP BY product_id;

   ```
   ```output

   +------------+---------------+
   | product_id | SUM(quantity) |
   +------------+---------------+
   |        101 |            10 |
   |        102 |            13 |
   |        103 |             6 |
   |        104 |             7 |
   +------------+---------------+
   ```

5. Insert a row in the view, and then query the view and the underlying table:
   ```sql
   INSERT INTO shipped_products VALUES (105, 10);

   SELECT product_id, SUM(quantity) 
   FROM shipped_products GROUP BY product_id;

   ```
   ```output

   +------------+---------------+
   | product_id | SUM(quantity) |
   +------------+---------------+
   |        105 |            10 |
   |        101 |            10 |
   |        102 |            13 |
   |        103 |             6 |
   |        104 |             7 |
   +------------+---------------+
   ```
   ```sql
   SELECT product_id, SUM(quantity) 
   FROM orders GROUP BY product_id;

   ```
   ```output

   +------------+---------------+
   | product_id | SUM(quantity) |
   +------------+---------------+
   |        105 |            10 |
   |        101 |            10 |
   |        102 |            13 |
   |        103 |             6 |
   |        104 |             7 |
   +------------+---------------+
   ```
   A new row with `product_id` as `105` is inserted in both the view and the underlying table.

6. Delete the row, and then query the view and the underlying table:
   ```sql
   DELETE FROM shipped_products WHERE product_id = 105;

   SELECT product_id, SUM(quantity) 
   FROM shipped_products GROUP BY product_id;

   ```
   ```output

   +------------+---------------+
   | product_id | SUM(quantity) |
   +------------+---------------+
   |        101 |            10 |
   |        102 |            13 |
   |        103 |             6 |
   |        104 |             7 |
   +------------+---------------+
   ```
   ```sql
   SELECT product_id, SUM(quantity) 
   FROM orders GROUP BY product_id;

   ```
   ```output

   +------------+---------------+
   | product_id | SUM(quantity) |
   +------------+---------------+
   |        101 |            10 |
   |        102 |            13 |
   |        103 |             6 |
   |        104 |             7 |
   +------------+---------------+
   ```
   The row with `product_id` as `105` is deleted from both the view and the underlying table.

## Atomic Table Swap Using a Writable View

Some applications need to periodically rebuild a table while a workload continues to perform read and write operations on the data. In these cases, renaming or dropping a table can lead to "table does not exist" errors. To avoid these errors, create a writable view on the table. Route all the read and write operations through the writable view and swap the underlying table atomically by altering the view definition.

> **📝 Note**: To support write operations, the view definition must satisfy the [Writable View Definition Limitations](https://docs.singlestore.com/#section-idm234617522129172.md).

Perform the following tasks:

1. Enable writable views.
   ```sql
   SET GLOBAL enable_writable_views = 1;
   ```

2. Create the required table and then create a writable view on the table. For example:
   ```sql
   CREATE TABLE base_data (
       key_col        INT PRIMARY KEY,
       value_col      VARCHAR(255),
       last_modified  DATETIME
       -- other columns...
   );

   CREATE VIEW ref_data AS
   SELECT key_col, value_col, last_modified
   FROM base_data;
   ```

3. Verify that the view is writable. Query the `VIEWS` information schema view as:
   ```sql
   SELECT TABLE_NAME, IS_UPDATABLE FROM information_schema.VIEWS
   WHERE TABLE_NAME='ref_data';

   ```
   ```output

   +-------------+--------------+
   | TABLE_NAME  | IS_UPDATABLE |
   +-------------+--------------+
   | ref_data    | YES          |
   +-------------+--------------+
   ```

4. Point the application to perform read and write operations on the view. For example:
   ```sql
   -- Reads --
   SELECT value_col
   FROM ref_data
   WHERE key_col = 42;

   -- Writes --
   UPDATE ref_data
   SET value_col = 'new value', last_modified = NOW()
   WHERE key_col = 42;
   ```
   Because the view is writable, these queries operate on the `base_data` table.

5. Rebuild the base table and switch the view to reference the new table. For example:
   ```sql
   -- Create a new table similar to the current base table --
   CREATE TABLE new_base LIKE base_data;

   -- Populate the new table --
   INSERT INTO new_base (...)
   SELECT ... ;

   -- Alter the view to point to the new table --
   ALTER VIEW ref_data AS 
   SELECT key_col, value_col, last_modified
   FROM new_base;

   -- Optionally drop the old table --
   DROP TABLE base_data;
   ```
   The `ALTER VIEW` statement is atomic, at any instant the view references either the original base table or the new table. Consequently, existing workloads querying the view do not encounter a missing table during the swap.

If the reference data is rebuilt on a fixed schedule and does not get updated between refresh cycles, you may use a standard (non-writable) view following the same pattern.

## Related Topics

* [ALTER VIEW](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/alter-view.md)
* [DROP VIEW](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/drop-view.md)

***

Modified at: June 11, 2026

Source: [/cloud/reference/sql-reference/data-definition-language-ddl/create-view/](https://docs.singlestore.com/cloud/reference/sql-reference/data-definition-language-ddl/create-view/)

(An index of the documentation is available at /llms.txt)