SQL in a Nutshell, 3rd Edition

Name

CREATE/ALTER VIEW Statement

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. 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. Almost any valid SELECT statement can define the contents of a view, though some platforms restrict the certain clauses of the SELECT statement and certain set operators.

In some cases, views can be updated, causing the view changes to be translated to the underlying data in the base tables. Some database platforms support a materialized view; that is, a physically created table that is defined with a query just like a view.

Tip

ALTER VIEW is not an ANSI-supported statement.

Platform	Command
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

SQL2003 Syntax

CREATE [RECURSIVE] VIEW view_name {[(column[, ...])] |
[OF udt_name [UNDER supertype_name
   [REF IS column_name {SYSTEM GENERATED | USER GENERATED | DERIVED}]
   [column_name WITH OPTIONS SCOPE table_name]]]}
AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

Keywords

CREATE VIEW view_name

Creates a new view using the supplied name.

RECURSIVE

Creates a view that derives values from itself. It must have a column clause and may not use the WITH clause.

[(column[, . . . ])]

Names all of the columns in the view. The number of columns declared here must match the number ...