Learning SQL, 2nd 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 federal IDs (Social Security numbers and corporate identifiers) in the customer table. The customer service department, for example, may need access to just the last portion of the federal ID in order to verify the identity of a caller, but exposing the entire number would violate the company’s privacy policy. Therefore, instead of allowing direct access to the customer table, you define a view called customer_vw and mandate that all bank personnel use it to access customer data. Here’s the view definition:

CREATE VIEW ...