SQL Create View

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.

```sql
CREATE VIEW view_name AS select_statement;
```
To understand this clearly, let's look at the following employees and departments tables.

```sql
+--------+--------------+--------+---------+
| 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 |
+--------+--------------+--------+---------+
```
Table: employees
```sql
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
```
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:

```sql
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:

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

```sql
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:

```sql
SELECT * FROM emp_dept_view;
```
As you can see how much time and effort you can save with the views.

<div class="callout callout-success mb-3">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.</div>

<div class="callout callout-info mb-3">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.</div>

## 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:

```sql
CREATE OR REPLACE VIEW view_name AS select_statement;
```
<div class="callout callout-info mb-3">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.</div>

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.

```sql
-- 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:

```sql
SELECT * FROM emp_dept_view ORDER BY emp_id;
```
You will see one more column salary in the resulting output, as follow:

```sql
+--------+--------------+--------+-----------------+
| 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            |
+--------+--------------+--------+-----------------+
```
<div class="callout callout-info mb-3">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.</div>

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

```sql
UPDATE emp_dept_view SET salary = '6000' 
WHERE emp_id = 1;
```
<div class="callout callout-info mb-3">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.</div>

## 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:

```sql
DROP VIEW view_name;
```
The following command will drop the view emp_dept_view from the database.

```sql
DROP VIEW emp_dept_view;
```