Learning SQL, 3rd Edition

Chapter 14. Views

Well-designed applications generally expose a public interface while keeping implementation details private, thereby enabling future design changes without impacting end users. When designing your database, you can achieve a similar result by keeping your tables private and allowing your users to access data only through a set of views. This chapter strives to define what views are, how they are created, and when and how you might want to use them.

What Are Views?

A view is simply a mechanism for querying data. Unlike tables, views do not involve data storage; you won’t need to worry about views filling up your disk space. You create a view by assigning a name to a select statement and then storing the query for others to use. Other users can then use your view to access data just as though they were querying tables directly (in fact, they may not even know they are using a view).

As a simple example, let’s say that you want to partially obscure the email address in the customer table. The marketing department, for example, may need access to email addresses in order to advertise promotions, but otherwise your company’s privacy policy dictates that this data be kept secure. Therefore, instead of allowing direct access to the customer table, you define a view called customer_vw and mandate that all nonmarketing personnel use it to access customer data. Here’s the view definition:

 CREATE VIEW customer_vw (customer_id, first_name, last_name, email  ) AS SELECT ...