CREATE VIEW (original) (raw)

1. Syntax

CREATE TEMP TEMPORARY VIEW IF NOT EXISTS schema-name . view-name ( column-name ) AS select-stmt ,

WITH RECURSIVE common-table-expression , SELECT DISTINCT result-column , ALL FROM table-or-subquery join-clause , WHERE expr GROUP BY expr HAVING expr , WINDOW window-name AS window-defn , VALUES ( expr ) , , compound-operator select-core ORDER BY LIMIT expr ordering-term , OFFSET expr , expr

common-table-expression:

table-name ( column-name ) AS NOT MATERIALIZED ( select-stmt ) ,

compound-operator:

UNION UNION INTERSECT EXCEPT ALL

expr:

literal-value bind-parameter schema-name . table-name . column-name unary-operator expr expr binary-operator expr function-name ( function-arguments ) filter-clause over-clause ( expr ) , CAST ( expr AS type-name ) expr COLLATE collation-name expr NOT LIKE GLOB REGEXP MATCH expr expr ESCAPE expr expr ISNULL NOTNULL NOT NULL expr IS NOT DISTINCT FROM expr expr NOT BETWEEN expr AND expr expr NOT IN ( select-stmt ) expr , schema-name . table-function ( expr ) table-name , NOT EXISTS ( select-stmt ) CASE expr WHEN expr THEN expr ELSE expr END raise-function

filter-clause:

function-arguments:

DISTINCT expr , * ORDER BY ordering-term ,

literal-value:

CURRENT_TIMESTAMP numeric-literal string-literal blob-literal NULL TRUE FALSE CURRENT_TIME CURRENT_DATE

over-clause:

OVER window-name ( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )

frame-spec:

GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS

raise-function:

RAISE ( ROLLBACK , expr ) IGNORE ABORT FAIL

type-name:

name ( signed-number , signed-number ) ( signed-number )

signed-number:

join-clause:

table-or-subquery join-operator table-or-subquery join-constraint

join-constraint:

USING ( column-name ) , ON expr

join-operator:

NATURAL LEFT OUTER JOIN , RIGHT FULL INNER CROSS

ordering-term:

expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST

result-column:

expr AS column-alias * table-name . *

table-or-subquery:

schema-name . table-name AS table-alias INDEXED BY index-name NOT INDEXED table-function-name ( expr ) , AS table-alias ( select-stmt ) ( table-or-subquery ) , join-clause

window-defn:

( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )

frame-spec:

2. Description

The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to thedatabase connection that created it and is automatically deleted when the database connection is closed.

If a schema-name is specified, then the view is created in the specified database. It is an error to specify both a schema-nameand the TEMP keyword on a VIEW, unless the schema-nameis "temp". If no schema name is specified, and the TEMP keyword is not present, the VIEW is created in the main database.

You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use anINSTEAD OF trigger on the view to accomplish the same thing. Views are removed with the DROP VIEW command.

If a column-name list follows the view-name, then that list determines the names of the columns for the view. If the column-namelist is omitted, then the names of the columns in the view are derived from the names of the result-set columns in the select-stmt. The use of column-name list is recommended. Or, ifcolumn-name list is omitted, then the result columns in the SELECT statement that defines the view should have well-defined names using the "AS column-alias" syntax. SQLite allows you to create views that depend on automatically generated column names, but you should avoid using them since the rules used to generate column names are not a defined part of the interface and might change in future releases of SQLite.

The column-name list syntax was added in SQLite versions 3.9.0 (2015-10-14).