Views

Understanding Views

Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

Why Use Views

You’ve already seen one use for views. Here are some other common uses:

• To reuse SQL statements.
• To simplify complex SQL operations. After the query is written, it can be reused easily, without having to know the details of the underlying query itself.
• To expose parts of a table instead of complete tables.
• To secure data. Users can be given access to specific subsets of tables instead of to entire tables.
• To change data formatting and representation. Views can return data formatted and presented differently from their underlying tables.

Creating Views

Views are created using the CREATE VIEW statement.

Using Views to Simplify Complex Joins

One of the most common uses of views is to hide complex SQL, and this often involves joins. Look at the following statement:

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num;

This statement creates a view named ProductCustomers, which joins three tables to return a list of all customers who have ordered any product. If you were to SELECT * FROM ProductCustomers, you’d list every customer who ordered anything.

To retrieve a list of customers who ordered product RGAN01 you can do the following:

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

This statement retrieves specific data from the view by issuing a WHERE clause. When the DBMS processes the request, it adds the specified WHERE clause to any existing WHERE clauses in the view query so that the data is filtered correctly.

Using Views to Filter Unwanted Data

Views are also useful for applying common WHERE clauses. For example, you might want to define a CustomerEMailList view so that it filters out customers without e-mail addresses. To do this, you can use the following statement

CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

Obviously, when sending e-mail to a mailing list you’d want to ignore users that have no e-mail address. The WHERE clause here filters out those rows that have NULL values in the _custemail columns so that they’ll not be retrieved.

View CustomerEMailList can now be used like any table.

SELECT *
FROM CustomerEMailList;

Using Views with Calculated Fields

Views are exceptionally useful for simplifying the use of calculated fields. The following statement retrieves the order items for a specific order, calculating the expanded price for each item:

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

To turn this into a view, do the following:

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems;

To retrieve the details for order 20008 (the output above), do the following:

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;