Appearance

Reference>Meta Database>SQL Command Reference>General DDL>CREATE VIEW

CREATE VIEW ​

The CREATE VIEW statement defines a view, which is a logical table based on one or more tables or views. A view itself does not contain any data. The tables on which a view is based are called base tables.

See also DROP VIEW for information on modifying or removing a view from the database, and SELECT, INSERT, UPDATE, and DELETE for more details on syntax.

Syntax ​

sql
CREATE [OR REPLACE]
  VIEW folder_name.view_name
  [({col_alias }
        [,{ col_alias } [ ... ] ]
    )
  ]
  AS subquery

Parameters ​

folder_name: The name of your folder.

view_name: The name of the view.

OR REPLACE: This clause recreates the view if it already exists. It can be used to modify an existing view definition without needing to drop or recreate the view.

col_alias: The name of the expression selected by the defining query of the view. The number of aliases must match the number of expressions selected by the view. Aliases must follow the naming rules for database schema objects and must be unique within the view.

See also Naming Rules for Database Objects.

If aliases are omitted, the database derives them from the columns or column aliases in the query. Therefore, aliases must be used when the query contains expressions instead of plain column names. Additionally, aliases are required if the view definition contains constraints.

AS subquery: This clause specifies the columns and rows from the table(s) on which the view is based. The select list of the subquery can include up to 1000 expressions.

Updatable Views ​

An updatable view is a view that can be used to insert, update, or delete rows from its base table. A view can be defined to be inherently updatable.

To be inherently updatable, a view must meet the following conditions:

  • Each column in the view must map to a column in a single table.
  • The view must not contain any of the following constructs:
    • Set operators (e.g., UNION, etc.)
    • A DISTINCT operator
    • Aggregate or analytic functions
    • A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
    • A subquery in a SELECT list
    • Joins, except in the following cases (see also The Concept of a Key-Preserved Table):
      • Any INSERT, UPDATE, or DELETE operation on a join view may affect only one underlying base table at a time.
      • An INSERT statement may not explicitly or implicitly reference columns from a non-key-preserved table.
      • All updatable columns in a join view must map to columns in a key-preserved table.
      • Rows from a join view may be deleted as long as exactly one key-preserved table exists in the join. The key-preserved table may be repeated in the FROM clause.

The Concept of a Key-Preserved Table ​

This concept is relevant in the context of join views.

A table is key-preserved if every key of the table is also a key of the result of the join. A key-preserved table retains its keys through a join.

It is not necessary for the key(s) of a table to be selected for it to be key-preserved. It is sufficient that, if the keys were selected, they would also be keys of the result of the join.

The key-preserved property of a table does not depend on the actual data in the table. It is instead a property of its schema. For example, if the prod table had at most one product in each product category, the prod_cat_id column would be unique in the result of a join between prod and prod_cat, but prod_cat would still not be a key-preserved table.

sql
CREATE TABLE mkw_doku.prod (id_prod INTEGER, prod_cat_id INTEGER, prod STRING);
CREATE TABLE mkw_doku.prod_cat (id_cat INTEGER, prod_cat STRING);
INSERT INTO mkw_doku.prod VALUES(1,1,'Blue Jeans'); 
INSERT INTO mkw_doku.prod VALUES(2,1,'Red Jeans'); 
INSERT INTO mkw_doku.prod VALUES(3,2,'Yellow Hat'); 
INSERT INTO mkw_doku.prod VALUES(4,2,'Red Hat');
INSERT INTO mkw_doku.prod_cat VALUES(1,'Jeans');
INSERT INTO mkw_doku.prod_cat VALUES(2,'Hat');
 
SELECT * FROM mkw_doku.prod;

SELECT pd.*,pdc.* FROM mkw_doku.prod pd, mkw_doku.prod_cat pdc WHERE pd.prod_cat_id=pdc.id_cat;
txt
+---------+-------------+------------+--------+----------+
| ID_PROD | PROD_CAT_ID | PROD       | ID_CAT | PROD_CAT |
+---------+-------------+------------+--------+----------+
| 1       | 1           | Blue Jeans | 1      | Jeans    |
| 2       | 1           | Red Jeans  | 1      | Jeans    |
| 3       | 2           | Yellow Hat | 2      | Hat      |
| 4       | 2           | Red Hat    | 2      | Hat      |
+---------+-------------+------------+--------+----------+

In this view, prod is a key-preserved table because ID_PROD is a key of the prod table and also a key of the join result. The prod_cat table is not key-preserved because ID_CAT is a key of the prod_cat table but not a key of the join result.

Example 1 | Simple View ​

sql
CREATE TABLE mkw_doku.prod (id_prod INTEGER, prod_cat_id INTEGER, prod STRING);
INSERT INTO mkw_doku.prod VALUES(1,1,'Blue Jeans'); 
INSERT INTO mkw_doku.prod VALUES(2,1,'Red Jeans'); 
INSERT INTO mkw_doku.prod VALUES(3,2,'Yellow Hat'); 
INSERT INTO mkw_doku.prod VALUES(4,2,'Red Hat');
 
SELECT * FROM mkw_doku.prod;
txt
+---------+-------------+------------+
| ID_PROD | PROD_CAT_ID | PROD       |
+---------+-------------+------------+
| 1       | 1           | Blue Jeans |
| 2       | 1           | Red Jeans  |
| 3       | 2           | Yellow Hat |
| 4       | 2           | Red Hat    |
+---------+-------------+------------+
sql
CREATE VIEW mkw_doku.my_view AS 
    (SELECT * FROM mkw_doku.prod);

SELECT * FROM mkw_doku.my_view;
txt
+---------+-------------+------------+
| ID_PROD | PROD_CAT_ID | PROD       |
+---------+-------------+------------+
| 1       | 1           | Blue Jeans |
| 2       | 1           | Red Jeans  |
| 3       | 2           | Yellow Hat |
| 4       | 2           | Red Hat    |
+---------+-------------+------------+
sql
CREATE OR REPLACE VIEW mkw_doku.my_view AS 
    SELECT * FROM mkw_doku.prod pd 
             WHERE pd.prod_cat_id = 1;

SELECT * FROM mkw_doku.my_view;
txt
+---------+-------------+------------+
| ID_PROD | PROD_CAT_ID | PROD       |
+---------+-------------+------------+
| 1       | 1           | Blue Jeans |
| 2       | 1           | Red Jeans  |
+---------+-------------+------------+

Example 2 | JOIN View ​

sql
CREATE TABLE mkw_doku.prod (id_prod INTEGER, prod_cat_id INTEGER, prod STRING);
CREATE TABLE mkw_doku.prod_cat (id_cat INTEGER, prod_cat STRING);
INSERT INTO mkw_doku.prod VALUES(1,1,'Blue Jeans'); 
INSERT INTO mkw_doku.prod VALUES(2,1,'Red Jeans'); 
INSERT INTO mkw_doku.prod VALUES(3,2,'Yellow Hat'); 
INSERT INTO mkw_doku.prod VALUES(4,2,'Red Hat');
INSERT INTO mkw_doku.prod_cat VALUES(1,'Jeans');
INSERT INTO mkw_doku.prod_cat VALUES(2,'Hat');
 
SELECT * FROM mkw_doku.prod;
txt
+---------+-------------+------------+
| ID_PROD | PROD_CAT_ID | PROD       |
+---------+-------------+------------+
| 1       | 1           | Blue Jeans |
| 2       | 1           | Red Jeans  |
| 3       | 2           | Yellow Hat |
| 4       | 2           | Red Hat    |
+---------+-------------+------------+
sql
SELECT * FROM mkw_doku.prod_cat;
txt
+--------+----------+
| ID_CAT | PROD_CAT |
+--------+----------+
| 1      | Jeans    |
| 2      | Hat      |
+--------+----------+
sql
CREATE OR REPLACE VIEW mkw_doku.my_view AS 
    SELECT pd.*, pdc.* FROM mkw_doku.prod pd, mkw_doku.prod_cat pdc 
                       WHERE pd.prod_cat_id=pdc.id_cat;
SELECT * FROM mkw_doku.my_view;
txt
+---------+-------------+------------+--------+----------+
| ID_PROD | PROD_CAT_ID | PROD       | ID_CAT | PROD_CAT |
+---------+-------------+------------+--------+----------+
| 1       | 1           | Blue Jeans | 1      | Jeans    |
| 2       | 1           | Red Jeans  | 1      | Jeans    |
| 4       | 2           | Red Hat    | 2      | Hat      |
| 3       | 2           | Yellow Hat | 2      | Hat      |
+---------+-------------+------------+--------+----------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.