A view is a virtual table in the database whose contents are defined by a query, as shown in Figure 14-1. To the database user, the view appears just like a real table, with a set of named columns and rows of data. But unlike a real table, a view does not exist in the database as a stored set of data values. Instead, the rows and columns of data visible through the view are the query results produced by the query that defines the view. SQL creates the illusion of the view by giving the view a name like a table name and storing the definition of the view in the database.
The view shown in Figure 14-1 is typical. It has been given the name REPDATA and is defined by this two-table query:
SELECT NAME, CITY, REGION, QUOTA, SALESREPS.SALES
FROM SALESREPS, OFFICES
WHERE REP_OFFICE = OFFICE
The data in the view comes from the SALESREPS and OFFICES tables. These tables are called the source tables for the view because they are the source of the data that is visible through the view. This view contains one row of information for each salesperson, extended with the name of the city and region where the salesperson works. As shown in the figure, the view appears as a table, and its contents look just like the query results that you would obtain if you actually ran the query.
Once a view is defined, you can use it in a SELECT statement, just like a real table, as in this query:
List the salespeople who are over quota, showing the name, city, and region for each salesperson.
SELECT NAME, CITY, REGION
FROM REPDATA
WHERE SALES > QUOTA
The name of the view, REPDATA, appears in the FROM clause just like a table name, and the columns of the view are referenced in the SELECT statement just like the columns of a real table. For some views, you can also use the INSERT, DELETE, and UPDATE statements to modify the data visible through the view, as if it were a real table. Thus, for all practical purposes, the view can be used in SQL statements as if it were a real table.
1. How the DBMS Handles Views
When the DBMS encounters a reference to a view in a SQL statement, it finds the definition of the view stored in the database. Then the DBMS translates the request that references the view into an equivalent request against the source tables of the view and carries out the equivalent request. In this way, the DBMS maintains the illusion of the view while maintaining the integrity of the source tables.
For simple views, the DBMS may construct each row of the view on the fly, drawing the data for the row from the source table(s). For more complex views, the DBMS must actually materialize the view; that is, the DBMS must actually carry out the query that defines the view and store its results in a temporary table. The DBMS fills your requests for view access from this temporary table and discards the table when it is no longer needed. Regardless of how the DBMS actually handles a particular view, the result is the same for the user—the view can be referenced in SQL statements exactly as if it were a real table in the database.
2. Advantages of Views
Views provide a variety of benefits and can be useful in many different types of databases. In a personal computer database, views are usually a convenience, defined to simplify database requests. In a production database installation, views play a central role in defining the structure of the database for its users and enforcing its security. Views provide these major benefits:
- Security. Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user’s access to stored data.
- Query simplicity. A view can draw data from several different tables and present it as a single table, turning multitable queries into single-table queries against the view.
- Structural simplicity. Views can give a user a personalized view of the database structure, presenting the database as a set of virtual tables that make sense for that user.
- Insulation from change. A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.
- Data integrity. If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets specified integrity constraints.
3. Disadvantages of Views
While views provide substantial advantages, there are also two major disadvantages to using a view instead of a real table:
- Performance. Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex multitable query, then even a simple query against the view becomes a complicated join, and it may take a long time to complete.
- Update restrictions. When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views cannot be updated; they are read-only.
These disadvantages mean that you cannot indiscriminately define views and use them instead of the source tables. Instead, you must in each case consider the advantages provided by using a view and weigh them against the disadvantages.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.