O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CREATE/ALTER VIEW Statement

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. 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

DB2

Supported, with variations

MySQL

Not supported

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required