Views

A view is an object that allows you to view data in a normal table, but in a different way. Views provide a method for looking at data in underlying tables without duplicating that data. During a SELECT, INSERT, UPDATE, or DELETE, views are used just like tables, with some restrictions.

Although a view is a separate, physical object, it does not store data like a table. Think of a view as a filter through which you can view a set of data in your database.

Here is an example of creating a view:

create    view    AuthorNameView    as
select    au_lname   LastName,   au_fname   FirstName
from        authors

The view creation consists of the CREATE VIEW syntax and then a normal SELECT. In this SELECT, I’ve provided alias names for the columns. When selecting from the view, you can reference those alias names as though they were the actual column names:

select   LastName, FirstName
from      AuthorNameView

LastName                                 FirstName
------------------------------           ---------------
White                                    Johnson
Green                                    Marjorie
Carson                                   Cheryl
[…]
Panteley                                 Sylvia
Hunter                                   Sheryl
McBadden                                 Heather
Ringer                                   Anne
Ringer                                   Albert
(23   row(s)   affected)

Views may be selected from without restriction. Any SELECT syntax that is legal against a table is legal against a view.

When you create a view, you may create it with many of the options that are available during a normal SELECT. However, there are some restrictions:

•  You may not use an ORDER BY or a COMPUTE clause when creating a view. You may order or compute when selecting from the view.

•  If you create aliases for the view’s column names, the aliases must be valid column names. This means they may not include spaces or punctuation, and are limited to 30 characters.

Here is the syntax for view creation:

CREATE VIEW <viewname> [WITH ENCRYPTION] AS
<SELECT…> [WITH CHECK OPTION]

WITH CHECK OPTION is discussed under the “Inserting into Views” heading. If you create a view WITH ENCRYPTION, the code to create the view is encrypted in the syscomments table so that it cannot be reverse-engineered. Using encryption is a hassle if it isn’t really needed; unless you feel strongly about ensuring that database users can’t see your SQL code, you shouldn’t use encryption.

Restricting Rows Using a View

A view can be created with a SELECT that includes a WHERE clause to restrict rows that a view returns. To create a view that displays only titles published by New Moon Books, try this query:

create  view  TitlesForNewMoon  as
select    *
from        titles
where      pub_id = ‘0736’

When you create views, you receive no result set, so there is no output shown for this view creation. TitlesForNewMoon can be used now, in lieu of the titles table, when you need information from titles for only New Moon Books. The view can be used in a join to titleauthor and authors, for example, to list authors that write for New Moon books:

select  rtrim(a.au_fname)  + ‘ ‘ +  a.au_lname  “Author Name”,  t.title
from        authors a join titleauthor ta on a.au_id = ta.au_id
              join TitlesForNewMoon t on ta.title_id = t.title_id

Author Name             title
------------------      ----------------------------------
Marjorie Green          You Can Combat Computer Stress!
Anne Ringer             Is Anger the Enemy?
Albert Ringer           Is Anger the Enemy?
Albert Ringer           Life Without Fear
Johnson White           Prolonged Data Deprivation: Four Case Studies
Charlene Locksley       Emotional Security: A New Algorithm
(6   row(s)   affected)

A view is treated just like a table. You may join to a view in the same way you would join to a table. You may also apply other SELECT syntax to a view, such as an ORDER BY or GROUP BY.

---

Tip:  A user could have permissions restricted so that he could select from the view, but not the base table. This lets the view restrict access to specific columns or specific rows in the table.

---

Joining Tables with a View

You may specify a SELECT statement in the view creation that joins tables together. Although this provides no performance benefit—when the view runs, it must access multiple tables and perform the work of the join—it does serve to make your SQL code simpler to read and write. This example creates a view that, earlier, created a temporary table. The temporary table actually duplicated the data, while the view provides a different way to look at the actual data in the authors, titleauthor, and titles tables:

create   view      ViewCA_titles_and_authors   as
select  a.au_id,     a.au_lname,  a.au_fname,  a.phone, a.contract,
                     ta.au_ord, ta.royaltyper, ta.title_id,
                     t.title, t.type, t.price, t.advance, t.royalty
from   authors a,    titles t, titleauthor ta
where  a.au_id = ta.au_id and ta.title_id = t.title_id and a.state  = ‘CA’

Selecting from ViewCA would allow you to select some of the columns from authors, titles, and titleauthor without performing the work of a join in your SQL.