# 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-id235461160410095.md) for more information. ## Syntax ```sql CREATE [DEFINER = { | CURRENT_USER }] [SCHEMA_BINDING = { ON | OFF }] VIEW [IF NOT EXISTS]  AS ``` ## Writable Views Writable views support insert, update, and delete operations. Inserting, updating, or deleting rows in a writable view also affects the rows in the underlying (base) table referenced in the view definition. Refer to [Writable View Definition Limitations](https://docs.singlestore.com/#section-id235461168168018.md) for the requirements to support the respective write operation. Writable views also support subselects and joins in the view definition, provided it meets the requirements. With support for joins in the view definition, the view can reference tables outside the target table to update. SingleStore also supports writing to [nested views](https://docs.singlestore.com/#section-id235461182926658.md). Refer to [Permissions Required](https://docs.singlestore.com/#section-id235461180549132.md) for the permissions required to create or perform write operations on a writable view. Writable views are not supported in `REPLACE`, `LOAD DATA`, and `INSERT ... ON DUPLICATE KEY UPDATE` statements. Materialized views are not writable. 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`, `UPDATE`, and `DELETE` queries depends on the view definition. Refer to [Writable View Definition Limitations](https://docs.singlestore.com/#section-id235461168168018.md) for more information. > **📝 Note**: Information schema views are not writable. ## Writable View Definition Limitations To support write operations, the view definition must satisfy the [Minimum Requirements](https://docs.singlestore.com/#section-id235461169161599.md). Additionally, to support `INSERT`, `UPDATE`, and `DELETE` operations, the view definition must meet the operation-specific requirements: * [INSERT-specific Requirements](https://docs.singlestore.com/#section-id235461177421317.md) * [UPDATE-specific Requirements](https://docs.singlestore.com/#section-id235461177474336.md) * [DELETE-specific Requirements](https://docs.singlestore.com/#section-id235461177524585.md) ## Minimum Requirements The view definition must conform to the following requirements (to support write operations): * The view definition must not contain the following: * Aggregate functions or window functions * `DISTINCT`, `UNION`, `UNION ALL`, `EXCEPT`, or `INTERSECT` clause * `GROUP BY`, `HAVING`, or `LIMIT` clause * References to non-table entities other than views, such as a derived table, in the `FROM` clause * Complex expression or derived (non-simple) column references in the `SELECT` subquery * Subselects in the `ORDER BY` clause * If the view contains joins, they must be inner-joins. Outer joins, such as, `LEFT`, `RIGHT`, `OUTER`, etc., are not supported. * The projection list must reference at least one simple column from the underlying table. * Subselects in the projection list must be independent; references to outer query columns is not supported for writable views. * Subselects in the `WHERE` or `ON` clause that reference the same target table being updated are not supported. However, subselects are allowed to be correlated with the outer query through column references until the subselect itself does not reference the target table in the `FROM` clause. For example, the following query shapes are supported: ```sql SELECT ... FROM t1 WHERE a > ( SELECT COUNT(*) FROM t2 WHERE t2.b = t1.b ); ``` * The target must not be an information schema view. * The target must ultimately be a base table or another writable view. ## INSERT-specific Requirements To support `INSERT` queries, the view definition must conform to the following restrictions in addition to the [minimum requirements](https://docs.singlestore.com/#section-id235461169161599.md): * 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. * The `SELECT`-list (projection list) in the view definition must only contain column references. Expressions, literals, functions, and subselects are not supported. * If the view definition contains a join, the columns in the `INSERT` query must only reference a single underlying table. Additionally, if specified, a single alias must be used. ## UPDATE-specific Requirements To support `UPDATE` queries, the view definition must conform to the following restrictions in addition to the [minimum requirements](https://docs.singlestore.com/#section-id235461169161599.md): * If the view definition contains a join: * The target table to update must be the leftmost table in the view definition. * The columns in the `UPDATE` query must only reference a single underlying table. Additionally, if specified, a single alias must be used. * Table aliases must be unique across the view definition and the `UPDATE` query, except for the alias of the target table itself. ## DELETE-specific Requirements In addition to the [minimum requirements](https://docs.singlestore.com/#section-id235461169161599.md), to support `DELETE` queries, the view definition must only contain a single table in `FROM` clause. ## Permissions Required The user that defines the view (view definer) must have the `SELECT`, `INSERT`, `UPDATE`, or `DELETE` permissions (or privilege) to the underlying table or tables in the `JOIN` clause of view definition to perform the respective operations on the view. Additionally, the view definer needs the `SELECT`, `INSERT`, `UPDATE`, or `DELETE` permissions to grant the respective permission on the view to other users. If a table referenced in the view definition has columns outside the projection list, the view definer must have `SELECT` permission on the table. Otherwise, no user is allowed to perform write operations on the view. Other users need the write (`INSERT`, `UPDATE`, or `DELETE`) privilege on 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 TO ; ``` If the view definer's `INSERT`, `UPDATE`, or `DELETE` permission to the underlying table is revoked (for example by`SUPER` or admin users), then the respective `INSERT`, `UPDATE`, or `DELETE` privilege is revoked for other users as well. ## Nested Views Fundamentally, the view definer needs write permission to the underlying table(s) and the view invoker needs write permission to the view itself. If the target is another view instead of a base table, the same permission requirements apply recursively, i.e., each view definer must have the corresponding `INSERT`, `UPDATE`, or `DELETE` permission to the underlying table/view. This requirement is continued for nested views and applies to each pair (view -> target table/view). For example, consider the following mapping that specifies the permission required by the view definer for the respective view to be writable: | Definer | View | Target Table/View | Permission Required | | ------- | ------- | ----------------- | ------------------------------------------- | | `user1` | `view1` | `table1` | `user1`requires write permission on`table1` | | `user2` | `view2` | `view1` | `user2`requires write permission on`view1` | | `user3` | `view3` | `view2` | `user3`requires write permission on`view2` | Write operations performed on any intermediate view in the nested view chain are ultimately applied to the underlying (base) table. ## 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/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for details. * SingleStore does not support MySQL’s `CREATE OR REPLACE VIEW` syntax. To modify an existing view, use `ALTER VIEW`. * By default, SingleStore restricts the ability to `CREATE`, `ALTER`, or `SELECT` from the view to the user who created the view and to `SUPER` users. 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/db/v9.1/reference/sql-reference/database-object-case-sensitivity.md) for more information. * This command can be run on the master aggregator node, or a child aggregator node (see [Node Requirements for SingleStore Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md) ). * This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information. * Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/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-id235461168168018.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/db/v9.1/reference/sql-reference/data-definition-language-ddl/alter-view.md) * [DROP VIEW](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/drop-view.md) *** Modified at: June 11, 2026 Source: [/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-view/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-view/) (An index of the documentation is available at /llms.txt)