SQL in a Nutshell

Name

CREATE VIEW

Synopsis

This statement creates a view, also known as a virtual table . A view acts just like a table but is actually defined as a query. Almost any valid SELECT statement can define the contents of a view, though an ORDER BY clause is usually prohibited.

When a view is referenced in a statement, the result set of the query becomes the content of the view for the duration of that statement. In some cases, views can be updated, causing the view changes to be translated to the underlying data in the base tables.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

Warning

Views even can be built upon other views, but this is inadvisable and usually considered bad practice.

SQL99 Syntax and Description

CREATE VIEW view_name [(column list)]
AS 
(SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION] )

Views are usually as effective as the query upon which they are based. That is why it is important to be sure that the defining SELECT statement is speedy and well-written.

A column list also may be specified after the view name. The optional column list contains aliases serving as names for each element in the result set of the SELECT statement.

The WITH CHECK OPTION clause is used only on views that allow updates to the base table. It ensures that only data that may be read by the view may be inserted, updated, or deleted by the view. For example, if a view of employees showed ...