SQL BASIC
SQL JOINS
SQL ADVANCED
SQL REFERENCE

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

CREATE VIEW view_name AS select_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            |
+---------+------------------+
Table: employees Table: departments

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

Tip: A view always shows up-to-date data! The database engine executes the SQL query associated with the view and recreates the data, every time a view is queried.

Note: In MySQL you can also specify the ORDER BY clause in a view definition. But, in SQL Sever a view definition cannot contain an ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.


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:

CREATE OR REPLACE VIEW view_name AS select_statement;

Note: When the OR REPLACE clause is used in CREATE VIEW statement, it will create a new view if the view does not exist, otherwise replaces an existing view.

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

Note: SQL Server doesn't support the OR REPLACE clause, therefore to replace the view you can simply drop that view and create a new one from stretch.


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;

Note: For insertability, the view must contain all columns in the base table that do not have a default value. Similarly, for updatability each updatable column in the view must correspond to an updatable column in a source table.


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:

DROP VIEW view_name;

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

  • DROP VIEW emp_dept_view;

Your Feedback:

We would love to hear from you, please drop us a line.