VIEW to Provide a Subset

1. Introduction

A view is a named query that allows us to present the data in a database in a different way. When we create a view, we are basically creating a query and then assigning it a name. Then we can query the view for simplicity. These named queries that we create do not store any data directly unless they are materialized views. Those are special types of views that store the data physically and refresh the data from the base tables that the views are dependent on.

2. VIEW Examples

A view can help simplify the complexity of a query as we will see in the upcoming tutorials. Since we can query a view, the view can be a complex query so we would not have to enter in the entire complex query each time. In addition, like a table, you can grant permission to users through a view that contains specific data that certain users are allowed to see. For example, if you have an employee table that has personal information that others should not be able to see like the salary, you could create a view that does not include the salary and allow others to query that view rather than the base table.

The statement would look like the following:

CREATE VIEW <viewname>
AS
SELECT <statement>;

For example, if we wanted to only have a list of the employee names and who they report to, we could create a view like:

CREATE VIEW employee_report
AS
SELECT first_name, last_name, employee_id, reports_to
FROM employee;

To query from this view, we would create a SELECT statement as if it were querying from a table:

SELECT * 
FROM employee_report;

If this were a query that we ran often, it would help simplify having to write out the query each time to include each of the columns.

If different employees wanted to query their customer list (through the support_rep_id), we could create a view for each:

CREATE VIEW customer_contact_3
AS
SELECT first_name, last_name, email 
FROM customer
WHERE support_rep_id = 3;

Instead of having to query that entire customer list and filtering out the data each time, the support_id_rep equal to 3 can query the customer_contact_3 view directly like:

SELECT * 
FROM customer_contact_3;

Otherwise, they would have to enter in the entire SELECT statement each time:

SELECT first_name, last_name, email 
FROM customer
WHERE support_rep_id = 3;

In this case, the view filters the columns (first_name, last_name, and email) as well as the rows (those that have the support_rep_id equal to 3). If anything changes in the customer table, since the view does not store any data, the updates would be reflected in the view immediately if we queried the view.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.