SQL CREATE VIEW Statement

A view is a virtual table based on the result-set of a SELECT statement.

What is a View?

In SQL, a VIEW is a virtual table based on the result-set of a SELECT statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, JOIN, and UNION statements to a view and present the data as if the data were coming from a single table.

Note: The database design and structure will NOT be affected by the functions, WHERE, JOIN, or UNION statements in a view.

Syntax

CREATE VIEW viewName AS SELECT columnName(s) FROM tableName WHERE condition;

Note: The database does not store the view data! The database engine recreates the data, using the view's SELECT statement, every time a user queries a view.

Using Views

A view could be used from inside a query, a stored procedure, or from inside another view. By adding functions, JOIN, UNION, etc., to a view, it allows you to present exactly the data you want to the user.

By using the tables "Books" and "Papers", we created the "Documents" view with the following SQL:

CREATE VIEW Documents AS SELECT Title, Author, Year FROM Books UNION ALL SELECT Title, Author, Year FROM Papers;

The "Books" table:

Title	Author	Publisher	Year	Price
الدورة الدموية	إبن النفيس	دار العلم	1650	230.50
Java 2	L. Johnston	Fast Press	2002	120.99
Linux and Unix	J. Sam	Fast Press	2004	210.99
Operating Systems	M. Stone	Coriolis	2005	167.00
Web Programming	K. Yariv	East Edition	2005	140.60
XML Language	M. Salim	Knowledge Press	2000	90.45

The "Papers" table:

Title	Author	Journal	Year
Garbage Collection	J. Schmidt	Modern Computing	2005
Parallel Computing	H. Saleh	High Technology	2003
Quantum Computation	M. Candor	Quanta Journal	2004
Serialization	J. Xue	High Technology	2004
XML Language	A. Khan	Programming journal	2002

We can query the view above as follows:

SELECT * FROM Documents;

The result is:

Title	Author	Year
الدورة الدموية	إبن النفيس	1650
Java 2	L. Johnston	2002
Linux and Unix	J. Sam	2004
Operating Systems	M. Stone	2005
Web Programming	K. Yariv	2005
XML Language	M. Salim	2000
Garbage Collection	J. Schmidt	2005
Parallel Computing	H. Saleh	2003
Quantum Computation	M. Candor	2004
Serialization	J. Xue	2004
XML Language	A. Khan	2002

Another example view shows only the documents published in 2004 or later. Note that this view select its data from another view called "Documents":

CREATE VIEW RecentDocuments AS SELECT * FROM Documents WHERE Year >= 2004;

We can query the view above as follows:

SELECT * FROM RecentDocuments;

The result is:

Title	Author	Year
Linux and Unix	J. Sam	2004
Operating Systems	M. Stone	2005
Web Programming	K. Yariv	2005
Garbage Collection	J. Schmidt	2005
Quantum Computation	M. Candor	2004
Serialization	J. Xue	2004

Test your SQL Skills

To test your SQL skills click here.