Introduction to SQL Views

SQL Tutorials 9 min read
SQL Views
Blog / SQL Tutorials / Introduction to SQL Views

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.

two people working together on laptops, sql views
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.

SQL 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.

dept emp, sql views

Let’s see what happens after we run the following query:

SELECT

    *

FROM

    dept_emp;

331603 rows returned, sql views

The table has more than 331,000 entries, as you can see in the picture above.

data science training

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.

d004 d005, sql views

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.31579 rows returned, sql views

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.

create view, sql views2. 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.

view name, sql views

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.

select, sql views

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.

max from date max to date

Executing the Query

Let’s execute the whole query and create our view.

0 rows returned, sql views

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.

employees table, sql views

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.

v_dept_emp_latest

Whereas the last one executes the SELECT statement embedded in the view, delivering the desired result.

sql view

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;

select from employees

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.

database with 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.

a view acts as a shortcut

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.

to_date 2025-06-05

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.

sql views

There is actually another tool in MySQL that will make your program run faster, MySQL indexes.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Work with Indexes in MySQL

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

×
Online Data Science Training
SAVE 60%