SQL Views   «Prev  Next»
Lesson 2What is a view?
Objective Understand what an SQL View is.

What is a View in SQL ?

Views are like windows to the data in your system. If you think of a view like a window to the outside world, the window always gives you the same relative look at things, but also shows changes that pass in front of it. Views are stored queries that you can call on later to retrieve information from the tables. The following series of images show this idea graphically.

CustomerTable explained in the following diagrams

1) Columns for a table named MyTable
1) Columns for a table named MyTable

2) You can think of a view as a window into the database. This particular view represents the statement;
2) You can think of a view as a window into the database. This particular view represents the statement; 
SELECT * FROM MyTable WHERE State = 'UT'

3) In one particular instance, you might use the view (i.e., look through the window) and see the customer name Jane Doe
3) In one particular instance, you might use the view (i.e., look through the window) and see the customer name Jane Doe, whose customer ID=1234

4) The next time you use the same view, (look through the same window) you may see different data.
4) The next time you use the same view, (look through the same window) you may see different data.

  1. Columns for a table named MyTable
  2. You can think of a view as a window into the database.
  3. In one particular instance, you might use the view (i.e., look through the window) and see the customer name Jane Doe
  4. The next time you use the same view, (look through the same window) you may see different data.

Database Schema

The people responsible for developing the database schema and those who write application programs for use by non-technical users have knowledge of the entire schema, including direct access to the base tables of the database. However, it is usually undesirable to have end users working directly with base tables, primarily for security reasons. The relational data model includes a way to provide IT users with their own view of the database. This view hides the details of the overall database design and prohibits direct access to the base tables which should not be accessed.

SELECT Statement

A view starts as a SELECT statement, with all the capabilities that you have come to know from SELECT.
You can also think of a view as a query that is remembered by the engine. The really neat thing about views is that you can refer to them as tables. For example, suppose you have the following table:

CustomerTable

CustomerID Lastname Firstname State

You can create a view of the table that includes only those customers with a state of "UT".
As usual, to create a basic SQL statement that does this, you would use the following: 
SELECT * FROM CustomerTable
WHERE State = 'UT'

Once you have this query, it would be nice to refer to it by name, retrieving the values from the query without having to write the whole SQL statement over again? That is where views come in. In Access, views are equivalent to queries. Queries can be saved, and queries can be the target of SELECT statements in other queries. You may have to determine the specific name used by your engine if it supports views. In some engines, you will hear views called stored queries, saved queries, or virtual views.

SEMrush Software