SQL CREATE VIEW Statement

In this tutorial you will learn how to create, update, and delete a view using SQL.

Creating Views to Simplify Table Access

A view is a virtual table whose definition is stored in the database. But, unlike tables, views do not actually contain any data. Instead, it provides a way to store commonly used complex queries in the database. However, you can use the view in a SQL SELECT statement to access the data just as you would use a normal or base table.

Views can also be used as a security mechanism by allowing users to access data through the view, rather than giving them direct access to the entire base tables.

Syntax

Views are created using the CREATE VIEW statement.

To understand this clearly, let's look at the following employees and departments tables.

+--------+--------------+--------+---------+
| emp_id | emp_name     | salary | dept_id |
+--------+--------------+--------+---------+
|      1 | Ethan Hunt   |   5000 |       4 |
|      2 | Tony Montana |   6500 |       1 |
|      3 | Sarah Connor |   8000 |       5 |
|      4 | Rick Deckard |   7200 |       3 |
|      5 | Martin Blank |   5600 |    NULL |
+--------+--------------+--------+---------+

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

Suppose that you want retrieve the id and name of the employees along with their department name then you need to perform the left join operation, as follow:

SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

Once you execute the above query, you'll get the output something like this:

+--------+--------------+-----------------+
| emp_id | emp_name     | dept_name       |
+--------+--------------+-----------------+
|      1 | Ethan Hunt   | Human Resources |
|      2 | Tony Montana | Administration  |
|      3 | Sarah Connor | Sales           |
|      4 | Rick Deckard | Finance         |
|      5 | Martin Blank | NULL            |
+--------+--------------+-----------------+

But, whenever you want to access this record you need to type the whole query again. If you perform such operations quite often, it becomes really inconvenient and annoying.

In such situation you can create a view to make the query results easier to access, as follow:

CREATE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

Now you can access the same records using the view emp_dept_view, like this:

SELECT * FROM emp_dept_view;

As you can see how much time and effort you can save with the views.

Replacing an Existing View

In MySQL, if you want to update or replace an existing view, you can either drop that view and create a new one or just use the OR REPLACE clause in CREATE VIEW statement, as follow:

The following SQL statement will replace or change the definition of the existing view emp_dept_view by adding a new column salary to it.

-- Syntax for MySQL Database 
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

After updating the view, if you execute the following statement:

SELECT * FROM emp_dept_view ORDER BY emp_id;

You will see one more column salary in the resulting output, as follow:

+--------+--------------+--------+-----------------+
| emp_id | emp_name     | salary | dept_name       |
+--------+--------------+--------+-----------------+
|      1 | Ethan Hunt   |   5000 | Human Resources |
|      2 | Tony Montana |   6500 | Administration  |
|      3 | Sarah Connor |   8000 | Sales           |
|      4 | Rick Deckard |   7200 | Finance         |
|      5 | Martin Blank |   5600 | NULL            |
+--------+--------------+--------+-----------------+

Updating Data Through a View

Theoretically, you can also perform INSERT, UPDATE, and DELETE on views in addition to the SELECT statement. However, not all views are updatable i.e. capable of modifying the data of an underlying source table. There are some restrictions on the updatability.

Generally a view is not updatable if it contains any of the following:

• The DISTINCT, GROUP BY or HAVING clauses.
• Aggregate functions such as AVG(), COUNT(), SUM(), MIN(), MAX(), and so forth.
• The UNION, UNION ALL, CROSSJOIN, EXCEPT or INTERSECT operators.
• Subquery in the WHERE clause that refers to a table in the FROM clause.

If a view satisfies these conditions, you can modify the source table using that view.

The following statement will update the salary of the employee whose emp_id is equal to 1.

UPDATE emp_dept_view SET salary = '6000' 
WHERE emp_id = 1;

Dropping a View

Similarly, if you no longer need a view, you can use the DROP VIEW statement to drop it from the database, as shown in the following syntax:

The following command will drop the view emp_dept_view from the database.

DROP VIEW emp_dept_view;