SQL Create View

Creating Views to Simplify Table Access

A view is like a virtual table stored in the database. Unlike actual tables, views don't store data themselves. Instead, they store complex queries that are commonly used. These views can be queried using SQL SELECT statements, just like regular tables.

Views also enhance security by allowing users to access data through the view, rather than directly accessing the underlying tables.

Syntax

Views are created using the CREATE VIEW statement.

To grasp this concept better, let's examine 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            |
+---------+------------------+

If you need to fetch the id and name of employees along with their department names, you would perform a left join operation, like this:

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;

After running the query above, the output will look 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            |
+--------+--------------+-----------------+

However, if you frequently need to access this record, typing the entire query repeatedly can be inconvenient and frustrating.

In such situations, you can create a view to simplify accessing the query results, like this:

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 in this way:

SELECT * FROM emp_dept_view;

Using views demonstrates how much time and effort can be saved.

Updating an Existing View

In MySQL, to modify or replace an existing view, you have two options: you can drop the view and create a new one, or you can use the OR REPLACE clause in the CREATE VIEW statement, like this:

The SQL statement below will modify 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 run the following statement:

SELECT * FROM emp_dept_view ORDER BY emp_id;

You will observe an additional column salary in the resulting output, as follows:

+--------+--------------+--------+-----------------+
| 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

In theory, you can also execute INSERT, UPDATE, and DELETE operations on views, besides the SELECT statement. However, not all views support this capability; some are restricted from modifying data in the underlying source table.

Typically, a view is not updatable if it includes 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 meets these criteria, you can update the source table using that view.

The next statement will adjust the salary of the employee with an emp_id of 1.

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

Removing a View

Similarly, when you no longer require a view, you can utilize the DROP VIEW statement to delete it from the database, as demonstrated in the following syntax:

This command will delete the view emp_dept_view from the database.

DROP VIEW emp_dept_view;