How Views Work
What actually happens in the database when you create a view, and what happens when you use it?
Creating a view means defining it in terms of its base tables. The definition of the view is stored in the system catalogs without any data. When you query or perform data modification commands through the view, you are accessing the data that is stored in
association with the underlying tables. In other words, creating a view does not generate a copy of the data, neither at the time the view is defined nor at the time the view is accessed.
When you query a view, it looks exactly like any other database table. You can display it in much the same way as you can any other table, with few restrictions.
Changing data through a view has some limitations (explained later in this chapter). For now, consider the simplest case: a view based on a single table. When you modify the data you see through a view, you are actually changing the data in the underlying base table. Conversely, when you change the data in the base tables, these changes are automatically reflected in the views derived from them.
Suppose you are interested only in books priced higher than $15 and for which an advance of more than $5,000 was paid. This straightforward SELECT statement would find the rows that qualify:
select * from titles where price > $15 and advance > $5000
Now suppose you have a slew of retrieval and update operations to do on this collection of data. You could, of course, combine the conditions shown in the previous query with any command that you issue. However, for convenience, you can create a view in which just the records of interest are visible:
create view hiprice as select * from titles where price > $15 and advance > $5000
When SQL receives this command, it does not actually execute the SELECT statement that follows the keyword AS. Instead, it stores the SELECT statement (which is, in fact, the definition of the view hiprice) in the system catalogs.
Now, when you display or operate on hiprice, SQL combines your statement with the stored definition of hiprice. For example, you can change all the prices in hiprice just as you can change any other table:
update hiprice set price = price*2
SQL actually finds the view definition in the system catalogs and converts this update command into the following statement:
update titles set price = price*2 where price > $15 and advance > $5000
In other words, SQL knows from the view definition that the data to be updated is in titles. It also knows that it should increase the prices only in those rows that meet the conditions on the price and advance columns given in the view definition.
Having issued the first update statementthe update to hipriceyou can see its effect either through the view or in the titles table. Conversely, if you had created the view and then issued the second update statement, which operates directly on the base table, the changed prices would also be visible through the view.
If you update a view's underlying table in such a way that more rows qualify for the view, they become visible through the view. For example, suppose you increase the price of the book You Can Combat Computer Stress to $25.95. Because this book now meets the qualifying conditions in the view definition statement, it becomes part of the view.
Naming View Columns
An issue to consider in how views work is view column names. Assigning alias names to a view's columns is required when either of these rules is fulfilled:
One or more of the view's columns is a complex expression: It includes an arithmetic expression, a built-in function, or a constant.
The view would wind up with more than one column of the same name (the view definition's SELECT statement includes a join, and the columns from the joined tables or views have the same name).
You can assign names to view columns or expressions in two ways:
Put the names inside parentheses following the view name, separated by commas. Make sure there is a name for every item in the SELECT list. If you do one column name this way, you must do them all the same way.
Allow the view to inherit names from the columns or display labels in the SELECT clause. Display labels are required for complex expressions and columns with name conflicts. Otherwise, you can leave column names as they are.
Figure 9.3 shows both methods.
Figure 9.3 Column Names in Views
Assigning column names in the CREATE VIEW clause can be illustrated with the currentinfo view discussed earlier in this chapter:
create view currentinfo (PUB#, TYPE, INCOME, AVG_PRICE, AVG_SALES) as select pub_id, type, sum(price*ytd_sales), avg(price), avg(ytd_sales) from titles group by pub_id, type
The computed columns in the SELECT list don't really have names, so you must give them new names in the CREATE VIEW clause or assign display labels in the SELECT clause. Otherwise, you'd have no way to refer to them. When you work with the view currentinfo, always use the new names, like this:
select PUB#, AVG_SALES from currentinfo
Using the old names, such as pub_id or avg(ytd_sales), won't work.
Duplicate Column Names
The second circumstance in which assigning new column names is required usually arises when there's a join in the SELECT statement and the joining columns have the same name. Even though they are qualified with different table names in the SELECT statement, you have to rename them in order to resolve the ambiguity:
create view cities (Author, Authorcity, Pub, Pubcity) as select au_lname, authors.city, pub_name, publishers.city from authors, publishers where authors.city = publishers.city
Of course, you are free to rename columns in a view definition statement whenever it's helpful to do so. Just remember that when you assign column names in the CREATE VIEW clause, the number and order of column names inside the parentheses has to match the number and order of items in the SELECT list.
Whether you rename a view column, its datatype and null status depend on how it was defined in its base table(s).
Creating Views with Multiple Underlying Objects
Another issue in how views work is the underlying objects. As you've seen, views can be based on one or many underlying objects. The objects can be connected with joins and/or subqueries and can be tables and/or views.
Using Subqueries and Joins
Here is an example of a view definition that includes three joins and a subquery. It finds the author ID, title ID, publisher, and price of each book with a price that's higher than the average of all the books' prices. (Including the author ID means that you'll see more than one row for books with multiple authors.)
create view highaverage as select authors.au_id, titles.title_id, pub_name, price from authors, titleauthors, titles, publishers where authors.au_id = titleauthors.au_id and titles.title_id = titleauthors.title_id and titles.pub_id = publishers.pub_id and price > (select avg(price) from titles)
Now that the view has been created, you can use it to display the results. In this example, you use the SELECT, WHERE, and ORDER BY clauses to tailor the information you see:
select price as Price, title_id as BookNum, au_id as Writer from highaverage where pub_name = 'Binnet & Hardley' order by price, title_id Price BookNum Writer ========== ======= =========== 29.99 MC2222 712-45-1867 29.99 TC7777 672-71-3249 29.99 TC7777 267-41-2394 29.99 TC7777 472-27-2349 40.95 TC3218 807-91-6654 [5 rows]
Deriving Views from Views
Let's use highaverage to illustrate a view derived from another view. Here's how to create a view that displays all the higher-than-average-priced books published by Binnet & Hardley:
create view highBandH as select * from highaverage where pub_name = 'Binnet & Hardley'
You can structure your SELECT statement to limit columns and order rows.
select price, title_id, au_id from highBandH order by price, title_id price title_id au_id ========== ======== =========== 29.99 MC2222 712-45-1867 29.99 TC7777 672-71-3249 29.99 TC7777 267-41-2394 29.99 TC7777 472-27-2349 40.95 TC3218 807-91-6654 [5 rows]
The process of combining a query on a view with its stored definition and translating it into a query on the view's underlying tables is called view resolution. Several problems can arise during this process. Be on the alert for issues arising from modifications of underlying tables or views:
Adding columns to underlying tables or views for a view defined as SELECT * (SQL expands the asterisk shorthand at view creation, not at execution). A query of the view runs without an error message, but results don't include columns added to the underlying object after the view was created.
Changing underlying tables or views by modifying column names or datatypes or by renaming or dropping the underlying objects. You'll probably get error messages when you query the view.
Updating through views so that rows in the view become ineligible for it. You can use the WITH CHECK OPTION clause to prevent "hiding" data through a view update.
A query of the view runs but does not display columns added to underlying objects after the view was created. This is because the asterisk is expanded at view creation, not at execution. In the following example, the addon table starts out with two columns:
create table addon (name char(5), num int) insert into addon values ('one' , 1)
After a view is created, someone adds a column to the table.
create view addonv as select * from addon alter table addon add status char(1) null
A query of the table shows three columns.
select * from addon name num status ===== =========== ====== one 1 (NULL) [1 row]
A query of the view shows only two columnsthe two that existed when the view was created.
select * from addonv name num ===== =========== one 1 [1 row]
Breaking Object Chains
Because views can be defined in terms of other views and tables, it's possible to wind up with a chain of objects, each dependent on another. Just as an actual chain can break at any link, so can a chain of objects. Any one of the views or tables in the chain might be redefined in such a way that its dependent views no longer make sense.
As an example, three generations of views derived from the authors table are shown here:
create view number1 as select au_lname, phone from authors where zip like '94%'
select * from number1 au_lname phone ======================================== ============ Bennet 415 658-9932 Green 415 986-7020 Carson 415 548-7723 Stringer 415 843-2991 Straight 415 834-2919 Karsen 415 534-9219 MacFeather 415 354-7128 Dull 415 836-7128 Yokomoto 415 935-4228 White 408 496-7223 Hunter 415 836-7128 Locksley 415 585-4620 [12 rows]
create view number2 as select au_lname, phone from number1 where au_lname > 'M'
select * from number2 au_lname phone ======================================== ============ Stringer 415 843-2991 Straight 415 834-2919 MacFeather 415 354-7128 Yokomoto 415 935-4228 White 408 496-7223 [5 rows]
create view number3 as select au_lname, phone from number2 where au_lname = 'MacFeather'
select * from number3 au_lname phone ======================================== ============ MacFeather 415 354-7128 [1 row]
What happens to number3 if you redefine number2 with different selection criteria?
A condition on a column available in number2's underlying table number1 (either au_fname or phone) works fine. The WHERE clause determines what, if any, data is seen through number3.
A condition such as a zip code matching "947nn" causes failure in creating number2 or displaying data through it because the zip column exists in neither number1 nor number2. With number2 in trouble, there's not much hope for retrieving data through number3.
Whatever you do to view number2, view number3 still exists and becomes usable again by dropping and re-creating view number2.
In short, some systems allow you to change the definition of an intermediate view without affecting dependent views as long as the target list of the dependent views remains valid. If you violate this rule, a query that references the invalid view produces an error message.
Using WITH CHECK OPTION
One of the problems with updating a view is that it is possible to change its values in such a way as to make the values ineligible for the view. For example, consider a view that displays all books with prices greater than $15.00. What happens if you update one of those book prices through the view, changing its price to $14.99? The optional WITH CHECK OPTION clause, which appears after the SELECT statement in the CREATE VIEW syntax, is designed to prevent such a problem:
CREATE VIEW view_name [(column_name [, column_name]...)] AS SELECT_statement [WITH CHECK OPTION]
The WITH CHECK OPTION clause tells SQL to reject any attempt to modify a view in a way that makes one or more of its rows ineligible for the view. In other words, if a data modification statement (UPDATE, INSERT, or DELETE) causes some rows to disappear from the view, the statement is considered illegal.
As an example, recall the view hiprice, which includes all titles with price greater than $15 and advance greater than $5,000:
create view hiprice as select * from titles where price > $15 and advance > $5000 select title, price, advance from hiprice title price advance ================================================== ========= ======= Secrets of Silicon Valley 40.00 8000.00 Sushi, Anyone? 29.99 8000.00 But Is It User Friendly? 42.95 7000.00 Life Without Fear 17.00 6000.00 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 40.95 7000.00 Computer Phobic and Non-Phobic Individuals: Behavior Variations 41.59 7000.00 [6 rows]
This statement updates one of the books visible through hiprice, changing its price to $14.99.
update hiprice set price = $14.99 where title = 'Secrets of Silicon Valley'
If WITH CHECK OPTION had been part of the definition of hiprice, the UPDATE statement would be rejected because the new price of Secrets of Silicon Valley would make it ineligible for the view. Because the definition of hiprice does not include the WITH CHECK OPTION clause, the UPDATE statement is accepted. But the next time you look at the data through hiprice, you'll no longer see Secrets of Silicon Valley.
In some systems, WITH CHECK OPTION can be included in a CREATE VIEW statement only if the view being defined is otherwise updatable. The checkv view is not updatable because it contains an aggregate expression. One system refuses to create it:
create view checkv (Type, AvPrice) as select type, avg(price) from titles group by type with check option Server: Msg 4510, Level 16, State 1, Procedure checkv, Line 1 Could not perform CREATE VIEW because WITH CHECK OPTION was specified and the view is not updatable.
Other systems are more forgiving. The same view is created without problem on ASA. However, you get an error message when you try to update data through it.
Adaptive Server Anywhere
update checkv set type = 'Psychology' where type = 'psychology' Error at line 1. Update operation attempted on non-updatable query.