Small. Fast. Reliable.
Choose any three.

SQL As Understood By SQLite

[Top]

CREATE VIEW

create-view-stmt:

syntax diagram create-view-stmt

select-stmt:

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 the database 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-name and the TEMP keyword on a VIEW, unless the schema-name is "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 an INSTEAD 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-name list 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. Note that the column-name list syntax is only supported in SQLite versions 3.9.0 (2015-10-14) and later.