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