In this tutorial, we’ll talk about SQL views.
This is a tool that is tricky at first. Grasping its functionality to the fullest can be a bit difficult without significant practice. Here, we will introduce you to this concept and show you how and why SQL views are used.
Side note: The database we are going to be using is the ‘employees’ database. Download it here.
Definition
A view is a virtual table whose contents are obtained from an existing table or tables, called base tables. The retrieval happens through an SQL statement, incorporated into the view.
To remember this explanation more easily, think of a view object as a view into the base table. The view itself does not contain any real data; the data is physically stored in the base table. The view simply shows the data contained in the base table.
Let’s provide an example and then we’ll discuss when views can be helpful.
An Example
We will focus on the “dept_emp” table that contains information about the department in which each employee is working, as well as the start and end date of their contract.
Let’s see what happens after we run the following query:
SELECT
*
FROM
dept_emp;
The table has more than 331,000 entries, as you can see in the picture above.
Why We Retrieved More Rows
Some employee numbers have been inserted more than once. This is because a new entry about the same employee has been recorded every time the employee changed departments. This results in more than one start and end date for such employees.
To verify this is the case, we could run a query that shows the employee numbers of people registered in the “dept_emp” table more than once. The code looks like this:
SELECT
emp_no, from_date, to_date, COUNT(emp_no) AS Num
FROM
dept_emp
GROUP BY emp_no
HAVING Num > 1;After we execute it, we will know how many such entries we have.
31,579.
How to Create SQL Views
Well, imagine you wanted to see a tabular output with the number of each employee shown only once, having aside their latest starting and ending date. In other words, you would like to visualize only the period encompassing the last contract of each employee.
The Syntax
The syntax to abide by is the following:
1. First, let’s type CREATE OR REPLACE VIEW. Of course, CREATE VIEW would suffice, but the “OR REPLACE” fragment ascertains that the code we are about to write will be executed even if we already have a view with the same name.
2. Second, we’ll have to add a name. Typically, we assign views with names starting with “V_” or “W_” to indicate the object is a view. Let’s call our view v_dept_emp_latest_date.
Important: Don’t forget to type the “AS” keyword before you expose the final part of the query, which is a SELECT statement. Namely, this SELECT statement will extract data from the base table that will populate the view.
So, the whole query will be:
CREATE OR REPLACE VIEW v_dept_emp_latest_date AS
SELECT
emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
FROM
dept_emp
GROUP BY emp_no;If we run the SELECT statement separately, we will obtain the tabular output that our view delivers every time when implemented.
As shown in the picture below, we managed to retrieve a three-column table with the last “ from_date and to_date registered for each employee, as defined by the MAX function.
Executing the Query
Let’s execute the whole query and create our view.
Are there any signs the operation was carried out smoothly apart from the green circle you can see in the picture above?
Well, we can look at the “SCHEMAS” section at the left side of the screen and expand the subsection of the “employees” table. 
You can see four object types, one of which is “Views”. The two small icons located to the right of its name provide certain information about it.
Whereas the last one executes the SELECT statement embedded in the view, delivering the desired result.
Another Way to Execute it
As a matter of fact, the view could be executed by typing the code that just appeared in the new SQL tab. It is a mere SELECT statement, where the database name containing the view must precede the name v_dept_emp_latest_date.
So, in our case:
SELECT * FROM employees.v_dept_emp_latest_date;
This is it from a technical perspective! We created a view that extracts the information we wanted, and we used two ways to do that.
Why We Need SQL Views
But why, in general, do we need SQL views? Doing that probably seems redundant. Can’t we just run the SELECT statement and obtain the desired output?
Well, imagine your database is used by a large web application which is accessed by many users.
Imagine you want to allow each user to see the database from the picture above. You can use a view which will also allow each user to see the result-set obtained after executing a certain query on their userspace. Thus, you can avoid typing and running the same SQL statement that extracts the required information every time a request from a user appears.
Saving Time
A view acts as a shortcut for writing the same SELECT statement every time a new request has been made.
Thus, it saves a lot of coding time.
Moreover, because it is written only once, the view occupies no extra memory.
Acting as a Dynamic Table
Finally, the view acts as a dynamic table because it instantly reflects data and structural changes in the base table. For instance, if you, as a data administrator, must update the end contract date of employee 10001, when you check the view, the new date value will appear; you will need to make no additional changes in the code composing the view.
Using SQL Views
To conclude, views are advantageous when used logically. In certain applications, the use of SQL views can definitely be beneficial. Nevertheless, don’t forget that they are not real, physical data sets, meaning we cannot insert or update the information that has already been extracted. They should be seen as temporary virtual data tables retrieving information from base tables.
There is actually another tool in MySQL that will make your program run faster, MySQL indexes.
***
Eager to hone your SQL skills? Check out our SQL course.
Next Tutorial: Work with Indexes in MySQL
