Practical SQL: Creating and Using Views

• By Judith S. Bowman
• Jun 7, 2002
• Sample Chapter is provided courtesy of Addison Wesley

This chapter is from the book
Practical SQL Handbook, The: Using SQL Variants, 4th Edition

In This Chapter

• With a View Toward Flexibility
• View Commands
• Advantages of Views
• How Views Work
• Data Modification Through Views
• Creating Copies of Data

With a View Toward Flexibility

Like the join operation, the view is a hallmark of the relational model. A view creates a virtual table from a SELECT statement and opens up a world of flexibility for data analysis and manipulation. You can think of a view as a movable frame or window through which you can see data. This metaphor explains why people speak of looking at data or of changing data ''through'' a view.

Previous chapters have demonstrated how to use a SELECT statement to choose rows, combine tables, add display labels, form groups, and make calculations until you've derived specific information in a specific form. Creating a view based on a SELECT statement gives you an easy way to examine and handle just the data you (or others) need—no more, no less. In effect, a view "freezes" a SELECT statement.

Views are not separate copies of the data in the table(s) or view(s) from which they're derived. In fact, views are called virtual tables because they do not exist as independent entities in the database as ''real'' tables do. (The ANSI term for a view is a viewed table; a native database table is a base table.) You can query views much as you query tables. Modifying data through views is restricted, however (see "Data Modification Through Views" later in this chapter).

The system catalogs store the definition of the view—the view's name and SELECT statement. When a user calls a view, the database system associates the appropriate data with it. A view presents the end result of this process, hiding all its technical underpinnings. A view's beauty lies in its transparency: Naive users aren't frightened by joins, crafty users aren't tempted to look at (or try to alter) data that is none of their business, and impatient users aren't slowed down by the need to type long SQL statements.