SQL Views

The SQL VIEW is a virtual table that does not physically exist, based on an SQL statement's result-set.

Unlike tables, the SQL VIEW does not contain any data.

A view can be created from one or multiple tables.

Views are generally used for the following purpose:

• To structure data in a certain way.
• To be used as a security mechanism by restricting access to data directly.
• To summarize data from different tables.

CREATE VIEW Syntax

The syntax of the CREATE VIEW statement is as follows:

CREATE VIEW view_name AS
SELECT column_list
FROM table_name
WHERE condition;

Parameters:

• view_name: The name of the view that you want to create.

Demo Table

To understand SQL views, let us suppose that we have the following "Employee" and "Department" tables:

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | 5       |
  | 1006   | James       | Johnson   | 8000      | 4       |
  | 1007   | Stephanie   | Miller    | 8500      | 8       |
  | 1008   | Nelson      | Lopez     | 7300      | 8       |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

SQL CREATE VIEW Example

Suppose that you want to select the employee's first and last names along with their department names. You will need to run the following SQL statement:

SELECT em.first_name, em.last_name, dep.dept_name
FROM employee AS em INNER JOIN department AS dep
ON em.dept_id = dep.dept_id;

If you execute the above SQL statement, you will have the following result-set:

+------------+-----------+------------------+
| first_name | last_name | dept_name        |
+------------+-----------+------------------+
| James      | Smith     | Customer Service |
| Maria      | Martinez  | Development      |
| Andrew     | Rodriguez | Finance          |
| Robert     | Hernandez | Development      |
| Donna      | Williams  | Marketing        |
| James      | Johnson   | Human Resources  |
| Stephanie  | Miller    | Sales            |
| Nelson     | Lopez     | Sales            |
+------------+-----------+------------------+

Every time you want to access the above result-set, you need to type the whole query again. It becomes annoying if you need to type the whole query very often.

If you are in such a situation, you can create an SQL VIEW to make the query's output easier to access, as follows:

CREATE VIEW employee_department_view AS
SELECT em.first_name, em.last_name, dep.dept_name
FROM employee AS em INNER JOIN department AS dep
ON em.dept_id = dep.dept_id;

Now, you can access the same result-set using the above "employee_department_view" view, as follows:

SELECT * FROM employee_department_view;

As you can see above, you can fetch records from the view like selecting rows from a table.

Note: A view always output up-to-date data. Every time a view is queried, the database system runs the SQL query associated with the view and returns the data.

SQL Updating a View

To update the definition of an SQL VIEW without dropping it. You can use the SQL CREATE OR REPLACE VIEW statement.

SQL CREATE OR REPLACE VIEW Syntax

The syntax of the SQL CREATE OR REPLACE VIEW statement is as follows:

CREATE OR REPLACE VIEW view_name AS 
SELECT column_list
FROM table_name
WHERE condition;

SQL CREATE OR REPLACE VIEW Example

The following SQL statement adds the "Salary" column to the "employee_department_view" view:

CREATE OR REPLACE VIEW employee_department_view AS
SELECT em.first_name, em.last_name, em.salay, dep.dept_name
FROM employee AS em INNER JOIN department AS dep
ON em.dept_id = dep.dept_id;

Now, if you want to access the new result-set of the view, you can use the following command:

SELECT * FROM employee_department_view;

After the execution, the result-set from the updated view will be as follows:

+------------+-----------+--------+------------------+
| first_name | last_name | salary | dept_name        |
+------------+-----------+--------+------------------+
| James      | Smith     | 6600   | Customer Service |
| Maria      | Martinez  | 9000   | Development      |
| Andrew     | Rodriguez | 9500   | Finance          |
| Robert     | Hernandez | 9600   | Development      |
| Donna      | Williams  | 7400   | Marketing        |
| James      | Johnson   | 8000   | Human Resources  |
| Stephanie  | Miller    | 8500   | Sales            |
| Nelson     | Lopez     | 7300   | Sales            |
+------------+-----------+--------+------------------+

SQL Dropping a View

To delete a view, you need to use the DROP VIEW statement.

SQL DROP VIEW Syntax

The syntax of the SQL DROP VIEW statement is as follows:

DROP VIEW view_name;

SQL DROP VIEW Syntax

The following SQL statement drops the "employee_department_view" view:

DROP VIEW employee_department_view;