CREATE VIEW command

The CREATE VIEW command defines a view.

Syntax

CREATE VIEW view_name (column_list)
     AS ( SELECT statement );

Keyword	Description
`view_name`	Name of view
`column_list`	List of columns to display in view
`statement`	Criteria by which you identify rows that you want to retrieve

Use

This statement creates a view. A view is an object that is treated as a table, but whose definition contains a query - a valid SELECT statement. Because the query may access more than one base table, a view may combine data from several tables. Views do not contain their own data. Because the rows of a view are, by definition, unordered, you cannot use ORDER BY when creating a view.

You reference a view in SQL statements just like base tables. When you reference the view in a statement, the output of the query becomes the content of the view for the duration of that statement. In cases in which views can be updated, the changes are transferred to the underlying data in the base tables.

The tables or views directly referenced in a query are called the simply underlying tables of the query or view. These, combined with all the tables they reference and all the subsequently referenced tables all the way down to and including the base tables that contain the data, are called the generally underlying tables. The base tables - the ones that don't reference any other tables, but actually contain the data - are called the leaf underlying tables. View definitions cannot be circular. That is, no view can be among its own generally underlying tables.

Views also cannot contain target specifications or a dynamic parameter specification. The list of columns is used to provide the columns with names that are used only in a given view. You can use it if you don't want to retain the names that the columns have in the underlying base tables. However, you must use it whenever:

Any of the two columns would otherwise have identical names
Any of the columns contain computed values or any values other than column values directly extracted from the underlying tables, unless an AS clause is used in the query to name them
There are any joined columns with distinct names in their respective tables, unless an AS clause is used in the query to name them

If you do name the columns, you cannot use the same column name twice in the same view. If you name the columns, you must name all of them, so the number of columns in the name list is the same as the SELECT clause of the contained query. You can use SELECT * in the query to select all columns; this command is converted internally to a list of all columns, so that if a column is added to an underlying table (using ALTER TABLE), your view remains valid.

Views can base their queries on other views, as long as the definition is not circular. Views cannot reference declared temporary tables, although global and created local ones are acceptable.