Home > Articles > Data > SQL

  • Print
  • + Share This
This chapter is from the book

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
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 statement—the update to hiprice—you 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.3Figure 9.3 Column Names in Views

Complex Expressions

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, 
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)
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
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 
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
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]

Resolving Views

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.

Adding Columns

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
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 columns—the 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
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
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
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.


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]...)]

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
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.

SQL Variants

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:

SQL Server
create view checkv (Type, AvPrice)
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.
  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020