|Lesson 3 || How do views in SQL work? |
| Objective || Understand how views are called. |
SQL View Inner Workings
Views are given a name, and you refer to that name whenever you want to use the view.
For example, if you named the query we just described MyView, you can get the results from the view by issuing:
SELECT * FROM MyView
You will learn how to name views soon.
For now, just be aware that views are named and then called by those names in your SQL commands.
At first look, this might seem minor. Suppose for a moment you have created a slew of queries that pull information from a view you
create. Assume for this discussion that the underlying query for the view is what we used earlier, and it filters the rows for states that are "UT":
SELECT * FROM MyTable
WHERE State = 'UT'
Develop Reports based on Views
You can then develop reports based on this view, without regard for how the SQL statement is created in the underlying query. Pretty nice, but this is not the real leverage offered by views.
A view is not stored with data and instead
- is stored under a name in the database itself
- along with a database query that will retrieve its data.
A view can therefore contain data from more than one table, selected rows, and selected columns.
The beauty of storing views in this way, however, is that whenever the user includes the name of the view in a (DML) data manipulation language statement, the DBMS executes the query associated with the view name and recreates the view's table. This means that the data in a view will always be current. A view table remains in main memory only for the duration of the data manipulation statement in which it was used. As soon as the user issues another query, the view table is removed from main memory to be replaced by the result of the most recent query. A view table is therefore a virtual table
Some end user DBMSs give the user the ability to save the contents of a view as a base table. This is a particularly undesirable feature, as there are no provisions for automatically updating the data in the saved table whenever the tables on which it was based change. The view table therefore will quickly become out of date and inaccurate.