Up to this point, we've been dealing with base objects — objects that have some level of substance of their own. In this chapter, we're going to go virtual (well, mostly anyway), and take a look at views.
Views have a tendency to be used either too much, or not enough — rarely just right. When we're done with this chapter, you should be able to use views to:
Reduce apparent database complexity for end users
Prevent sensitive columns from being selected, while still affording access to other important data
Add additional indexing to your database to speed query performance — even when you're not using the view the index is based on
A view is, at its core, really nothing more than a stored query. What's great is that you can mix and match your data from base tables (or other views) to create what will, in most respects, function just like another base table. You can create a simple query that selects from only one table and leaves some columns out, or you can create a complex query that joins several tables and makes them appear as one.
The syntax for a view, in its most basic form, is a combination of a couple of things we've already seen in the book — the basic
CREATE statement that we saw back in Chapter 5, plus a
SELECT statement like we've used over and over again:
CREATE VIEW <view name> AS <SELECT statement>
The preceding syntax just represents the minimum, of course, but it's still all we need in a large percentage of the situations. The more extended ...