Database Reference
In-Depth Information
22
In this chapter you learn exactly what views are, how they work, and when they
should be used. You also see how views can be used to simplify some of the SQL
operations performed in earlier chapters.
Views are virtual tables. Unlike tables that contain data, views simply contain
queries that dynamically retrieve data when used.
The best way to understand views is to look at an example. Back in Chapter
15, “Joining Tables,” you used the following
SELECT
statement to retrieve
data from three tables:
▼
Input
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
That query was used to retrieve the customers who had ordered a specific
product. Anyone needing this data would have to understand the table struc-
ture, as well as how to create the query and join the tables. To retrieve the
same data for another product (or for multiple products), the last
WHERE
clause
would have to be modified.
Now imagine that you could wrap that entire query in a virtual table called
productcustomers
. You could then simply do the following to retrieve the
same data:
▼
Input
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
