VIEW to Simplify Queries

1. Combining Data

Beyond just querying from a single table, you can also combine data from multiple tables as it is based on a select statement. For example, seeing the support_rep_id may not be extremely useful in an organization unless you know who that value belongs to. Instead, you could include the employee’s name that is the support rep similar to the following:

CREATE VIEW customer_contact
AS
SELECT customer.*, employee.first_name as support_first_name, employee.last_name as support_last_name
FROM customer, employee
WHERE customer.support_rep_id = employee.employee_id;

If we queried the customer_contact view, it would look like the following:

SELECT * 
FROM customer_contact;

We could further add to the query to include the necessary columns:

SELECT first_name, last_name, support_first_name, support_last_name 
FROM customer_contact;

If we wanted to write this out using the tables, we would have to do the following each time:

SELECT customer.first_name, customer.last_name, employee.first_name as support_first_name, employee.last_name as support_last_name
FROM customer, employee
WHERE customer.support_rep_id = employee.employee_id;

2. Complex Query Example

Let us take a look at a bit more complex query with the use of more than 2 tables. So far, when we query the tables for the tracks, we are looking just at the id values. This is not telling as you may want to get the artist’s name, album title, and track name at the same time. Creating a view for this purpose can simplify this process:

CREATE VIEW artist_album_track
AS
SELECT artist.name as artist_name, album.title as album_title, track.name as track_name
FROM artist 
INNER JOIN album ON artist.artist_id = album.artist_id
INNER JOIN track ON album.album_id = track.album_id;

Rather than querying the tables to get that list:  

SELECT artist.name as artist_name, album.title as album_title, track.name as track_name
FROM artist 
INNER JOIN album ON artist.artist_id = album.artist_id
INNER JOIN track ON album.album_id = track.album_id;

We can simply query the view directly like:

SELECT * 
FROM artist_album_track;

If we wanted to add some filters into our SELECT statement such as only listing the rows that belong to AC/DC, instead of doing:

SELECT artist.name as artist_name, album.title as album_title, track.name as track_name
FROM artist 
INNER JOIN album ON artist.artist_id = album.artist_id
INNER JOIN track ON album.album_id = track.album_id
WHERE artist.name = 'AC/DC';

We would query the view like:

SELECT * 
FROM artist_album_track
WHERE artist_name = 'AC/DC';

The second option just greatly simplifies the query without having to join each of the tables together.

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.