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

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