Create an unmaterialized view.

Syntax

CREATE
    [DEFINER = { <user> | CURRENT_USER }]
    [SCHEMA_BINDING = { ON | OFF }]
    VIEW <view_name>
    AS <select_statement>

Remarks

• Views in SingleStore DB are not writable. Attempts to INSERT, UPDATE, or DELETE data in a view will result in an error. Instead, use ALTER VIEW or manipulate data directly in the underlying tables from which the view is constructed.
• SingleStore DB will always attempt to merge queries that refer to a view with the original view definition such that portions of the view definition replace semantically equivalent portions of the query. This default behavior is similar to MySQL’s ALGORITHM = MERGE option as part of CREATE VIEW. When a merge is not possible, SingleStore DB creates an internal temporary table.
• SingleStore DB does not support MySQL’s CREATE OR REPLACE VIEW syntax. To modify an existing view, use ALTER VIEW.
• By default, SingleStore DB 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.
• Columns used in a view definition cannot be dropped. Running DROP TABLE on a table referenced by a view will give an error. Running ALTER TABLE DROP COLUMN col will give an error when col is referenced by a view. Columns not referenced by any view can be dropped.
• 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 DB which user should be used 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; you would need to drop the view 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 the Database Object Case Sensitivity topic for more information.
• This command can be run on the master aggregator node, or a child aggregator node (see Node Requirements for SingleStore DB Commands ).

Examples

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

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

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

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).

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;

Related Topics

• ALTER VIEW
• DROP VIEW

Was this Article Helpful?

Thanks For Your Feedback!