VIEW

This SQL tutorial explains how to create, update, and drop SQL VIEWS with syntax and examples.

DESCRIPTION

The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is created by a SQL statement that joins one or more tables.

CREATE SQL VIEW

Syntax

The syntax for the SQL CREATE VIEW Statement is:

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

view_name

The name of the SQL VIEW that you wish to create.

WHERE conditions

Optional. The conditions that must be met for the records to be included in the VIEW.

Example

Here is an example of how to use the SQL CREATE VIEW:

CREATE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'IBM';

This SQL CREATE VIEW example would create a virtual table based on the result set of the select statement. You can now query the SQL VIEW as follows:

SELECT *
FROM sup_orders;

UPDATE SQL VIEW

You can modify the definition of a SQL VIEW without dropping it by using the SQL CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the SQL CREATE OR REPLACE VIEW Statement is:

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  [WHERE conditions];

Example

Here is an example of how you would use the SQL CREATE OR REPLACE VIEW Statement:

CREATE or REPLACE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'Microsoft';

This SQL CREATE OR REPLACE VIEW example would update the definition of the SQL VIEW called sup_orders without dropping it. If the SQL VIEW did not yet exist, the SQL VIEW would merely be created for the first time.

DROP SQL VIEW

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

Syntax

The syntax for the SQL DROP VIEW Statement is:

DROP VIEW view_name;

view_name

The name of the view that you wish to drop.

Example

Here is an example of how to use the SQL DROP VIEW Statement:

DROP VIEW sup_orders;

This SQL DROP VIEW example would drop/delete the SQL VIEW called sup_orders.