SQL Views   «Prev  Next»
Lesson 3 How do views in SQL work?
ObjectiveUnderstand how views are called.

SQL View Inner Workings

To call a "view" that was previously generated from an Employee table in SQL, you would use the `SELECT` statement, which is fundamental in SQL for retrieving data from a database. Here is a generic example assuming the view is named `EmployeeView`:
SELECT * FROM EmployeeView;

This statement selects all columns from the `EmployeeView`. The asterisk `*` is a wildcard character representing all columns. However, in a professional setting, it's often recommended to specify only the columns you need, both for clarity and performance reasons. Suppose you are interested in retrieving only the employee's ID, name, and department from the view. In that case, the query would be:
SELECT EmployeeID, EmployeeName, Department 
FROM EmployeeView;

In this example, `EmployeeID`, `EmployeeName`, and `Department` are column names assumed to be part of the `EmployeeView`. Remember that a view in SQL is like a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can use a view to simplify complex queries, encapsulate queries against certain tables, and present a different representation of the data.

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:

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":

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.

View Mechanism

A view is not stored with data and instead
  1. is stored under a name in the database itself
  2. 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.
Note: 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.

SEMrush Software