SQL Views Simplified

We’ll try to cover SQL Views in this article

Image from flaticon

What is a View?

View is a database object which is built using a SQL query. View is a stored SELECT query which functions like a virtual table. The SELECT query can contain data from one or more tables, it can also contain WHERE, GROUP BY , ORDER BY clauses. Views do not take up extra memory and are not stored but can be used by their names and can be further queried using SQL statements.

When to use a View?

Some tables contain confidential columns which should not be accessible to some users. We cannot share the entire table with those users. In this case, we can create a view with only the required columns and share the view with the users.

Sometimes, queries become very complex and lengthy. If we need to run the same complex query multiple times, we can create a view using this query, and run the view.

In some cases, the end users just want the desired results(they don’t want to know the working). Here, views can be used as an abstraction of a query. The complexities, joins, etc. are hidden from the end users. Users simply need to query the view.

How to create a View?

CREATE VIEW view_name
AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;

Query a view:

SELECT * FROM view_name WHERE condition;

Let’s say, we have a table named employee

CREATE VIEW emp
AS
  SELECT * FROM employee;   
SELECT * FROM emp;

Update a View’s definition

CREATE OR REPLACE VIEW view_name
AS 
QUERY;

With this syntax, we can add new columns to our view, remove columns from our view.

Name of columns, order of columns and data type of columns cannot be changed. New columns can be added only at the end of the column list.

Let’s say we have another table named department

CREATE OR REPLACE VIEW emp
AS
  SELECT e.*, d.dept_name 
  FROM employee e JOIN department d 
  ON e.dept_id = d.dept_id;

We have added a new column dept_name from the department table to our view by using a JOIN .

Some things to note:

Let’s add a new column to our employee table:

ALTER TABLE employee
ADD COLUMN job_profile VARCHAR(50);

Now, let’s query our view emp :

We see that, the new column job_profile which was added to the employee table did not reflect in our emp view. This is because, while creating a view, SQL implicitly notes the column structure at that time of the tables which are used in the query. So, to update the column structure, we need to recreate the view.

***The job_profile column has been dropped for the rest of the article

View will show the latest records inserted but not the latest table structure.

Updating underlying table using Views:

It is not recommended to update tables using views, but it is still possible. To update a table on which the view is based on, some conditions need to be met:

• Query should not contain DISTINCT keyword
• Query should not contain GROUP BY, ORDER BY clause
• Query should not contain WITH clause
• Query should not contain Window functions

Generally, while updating or inserting into or deleting from a table, these conditions apply there as well. So it seems natural to have these conditions.

Updating view:

Remember that now our view is created from two tables. So UPDATE, INSERT operations are possible when we try to update columns from one base table only. In our case either columns from employee table or columns from department table.

UPDATE emp
SET salary=10000
WHERE emp_id=3;

Now, if we query the employee table, we can se that updating the view has updated the underlying table on which the view is based on:

If we try to update columns which belong to two different base tables:

Inserting into a view:

INSERT INTO emp(emp_id, emp_name, dept_id, salary) 
VALUES(30, "SD", 3, 3000);

We have mentioned fields from the employee table only and hence this INSERT statement is possible.

Deleting from a view:

Deleting from a view which is created from more than one tables is not possible.

WITH CHECK OPTION

A new column named city has been added to the employee table

Let’s create a new view:

CREATE VIEW Mumbai_emp
AS
  SELECT * 
  FROM employee 
  WHERE city="Mumbai";

This view contains records of employees who belong to Mumbai. Now, if we try to insert a new employee from Delhi to this view:

While accessing the view, we can only see employees from Mumbai, but while inserting into the view, we can add employees from any city.

To enforce the condition present in the WHERE clause, we can use the WITH CHECK OPTION. Rows cannot be modified in a way that causes them to vanish from the view.

CREATE OR REPLACE VIEW Mumbai_emp
AS
  SELECT * 
  FROM employee 
  WHERE city="Mumbai"
WITH CHECK OPTION;

If we try to insert a new employee from Delhi:

References:

Views in SQL-Youtube

Further reading:

https://www.informit.com/articles/article.aspx?p=130855&seqNum=4