Learning Journey in SQL

From A to SQL: A comprehensive overview of views

Welcome back to the “From A to SQL” series. If you are new here, this series is all about building a foundation knowledge base of SQL for data analysis and manipulation. I have covered a whole range of topics so far, with my latest chapter on performing a backup using the MySQL graphical user interface, which you can read here.

In this article, I will be walking you through SQL views, distinguishing between simple and complex views, and highlighting the benefits of using them. Any examples will use the business database that I have been creating along the way. If you want to follow along and would like the previous code, simply head to the other articles, staring at chapter 2.

Photo by Luca Bravo on Unsplash

Firstly, what is a view?

A view in SQL is a virtual table, which is created from a query on one or more underlying tables in the database. We refer to underlying tables as ‘base’ tables, and we can select all columns and rows from the base tables in the database, or a subset based on a specified criterion. Importantly, a view does not contain any data, just the query statement used to retrieve the results.

So, what’s the difference between a simple view and a complex view?

A simple view is when a view uses a single base table. A simple view cannot contain aggregations such as SUM() and COUNT() and cannot contain columns which are NOT NULL. On the other hand, a complex view can be created using multiple base tables, and contain logic such as joins, order by and group by clauses.

The other important distinction is that simple views are updatable. This means that Data Manipulation Language (DML) operations, such as Insert, Update and Delete can be performed on them. I will cover an important clause that will restrict this later in the article.

Why do I need to learn views? What are they used for anyway?

Reason 1: Security and Restriction of Data Access

The most common use case of a view is for security and restriction of access to data for different users. As the view doesn’t actually store any data, we can grant a user access to a view so they can see a subset of data, but they won’t have access to the table where this data is stored. We can also create multiple views for the same base table, and those can be used to grant different users a different view of the same data, depending on their role.

Reason 2: Improved user experience

Views are also used to simplify the experience for a user. We already know that a view is a virtual table, which means we can perform SELECT operations on it, just like on any other table. A user can select the information they need from multiple tables using a view, without needing to know how to perform complex joins, to retrieve their information.

Reason 3: Reusability of code

A view allows a user to see a subset of data from base tables, without needing to rewrite the SELECT query repeatedly. If the query that forms a view is complex, this is a great way to reuse the same query.

Can I update a view?

The simple answer is yes and no. Only a simple view is updatable, because there are a lot of restrictions to what can make a view updatable. For example, if your view has aggregations, having clause, group by clause, a subquery and certain types of joins, your view cannot be updated. This is because DML operations can only affect one base table at a time, and we cannot update data in derived columns (such as columns from a CONCAT() function).

How do I create a view?

You may be happy to hear that creating a view is simple. The complexity increases with the complexity of the query you are using to retrieve your data. On the most basic level, the syntax for a view is:

CREATE VIEW view_name 
AS
 SELECT column_1, column_2, column3 …
 FROM table_name
 WHERE condition;

What if I want to change the definition of my view or delete it?

To delete a view, simply use the following syntax:

DROP VIEW view_name;

To change the definition of a view you have already created, you can use the following syntax:

CREATE OR REPLACE VIEW view_name 
AS
  SELECT column_1, column_2, column3 …
 FROM table_name
 WHERE condition;

Now let’s use what we know so far in an example.

Ready? Set? CREATE VIEW.

For these examples, we will be using the business database. Currently, our database has 5 tables: customer, item, orders, supply, and employees. Our employees table has columns relating to employees ID, first name, last name, role title, manager ID and information about their salary. We want to restrict others from seeing the sensitive data, which is salary information, so we will create a view consisting of a subset of employee data, using the employee table as a base table.

CREATE OR REPLACE VIEW employee_view 
AS 
 SELECT e1.E_ID AS 'Employee_ID',
   CONCAT(e1.First_Name, ' ', e1.Last_name) AS 'Employee_Full_Name', 
            e1.Role_Title AS 'Employee_Role_Title', 
            CONCAT(e2.First_Name, ' ', e2.Last_name) AS 'Manager_Full_Name', 
            e2.Role_Title AS 'Manager_Role_Title'
            FROM employees e1
            LEFT JOIN employees e2 ON e1.Manager_ID = e2.E_ID;

We can query this view as we would any other table. Therefore, when we SELECT * FROM employee_view, we get the following result:

SQL output grid. Image created by author.

Likewise, we can add a WHERE clause with a LIKE operator to generate the following result:

SQL output grid. Image created by author.

Now, we want to create another view, where we are interested in seeing our Sales employees only.

CREATE OR REPLACE VIEW sales_employees 
AS 
 SELECT E_ID AS 'Employee_ID',
   First_Name AS 'Employee_First_Name',
            Last_name AS 'Employee_Last_Name', 
   Role_Title AS 'Employee_Role_Title'
            FROM employees 
            WHERE Role_Title = 'Sales';

Our view holds the following information:

SQL output grid. Image created by author.

What would happen if we inserted a new staff member, called Anna Smith who has a role of Senior Sales into the view?

INSERT INTO sales_employees (Employee_ID, Employee_First_Name, Employee_Last_Name, Employee_Role_Title)
VALUES ('E5', 'Anna', 'Smith', 'Senior Sales');

SELECT * FROM sales_employees;

SQL output grid. Image created by author.

As our view contained a WHERE clause, which restricted the data to only employees with a role title of Sales, Anna Smith doesn’t show up. An inexperienced user may think the data was not inserted or it was deleted somehow. In fact, the data has been added, but we cannot see it through the view. If we SELECT * FROM the employees base table, we can see that Anna Smith has in fact been added into the base table.

SQL output grid. Image created by author.

To stop this from happening, we can use a WITH CHECK OPTION. This option stops insertion of new rows of data into a view, where that data does not satisfy the WHERE clause in the CREATE VIEW statement.

CREATE OR REPLACE VIEW sales_employees 
AS 
 SELECT E_ID AS 'Employee_ID',
   First_Name AS 'Employee_First_Name',
            Last_name AS 'Employee_Last_Name', 
   Role_Title AS 'Employee_Role_Title'
            FROM employees 
            WHERE Role_Title = 'Sales'
            WITH CHECK OPTION;

Now if we try and insert an employee John Jones, who also has a role of Senior Sales, we will encounter an error.

INSERT INTO sales_employees (Employee_ID, Employee_First_Name, Employee_Last_Name, Employee_Role_Title)
VALUES ('E6', 'John', 'Jones', 'Senior Sales');

SELECT * FROM sales_employees;

SQL output grid. Image created by author

Congratulations! You are one step closer to advancing your SQL skills and knowing more about views. A further challenge is learning about indexes to speed up views and learning about granting read rights to tables. Let me know in the comments if you would like me to cover this in the next article.

Keep your eyes peeled for the next sequel of my learning journey :)

Go from SELECT * to interview-worthy project. Get our free 5-page guide.