SQL Views

Introduction to SQL Views

Views in SQL are defined as those result sets that work as a stored query on data, and acts as a pre-established query command which is stored by the SQL server in the database dictionary. A view representing a subset of the data present in a table, and performing functions such as joining and simplification of multiple tables into a single table, data aggregation, handling data complexity, providing security etc., The most important aspect being that they taking very minimal memory as a storage space.

Syntax to Create View

A View can be created using CREATE VIEW statement as below:

CREATE VIEW VIEW_NAME AS
SELECT column1, column2, column3.......
FROM table_name WHERE [condition];

Creating a view in SQL with Examples:

Let us consider the below table EMPLOYEE:

We can create a view from the above table as below.

CREATE VIEW EMLOYEE_VW AS
SELECT NAME, AGE, SALARY
FROM EMPLOYEE;

In the above view EMPLOYEE_VW, the below columns will be created.

SELECT * FROM EMPLOYEE_VW;

We can create views from more than one table.

Let us consider another table DEPARTMENT as below.

We can create a view from the above EMPLOYEE and DEPARTMENT tables as below:

CREATE VIEW DEPT_VIEW AS
SELECT EMPLOYEE.ID, EMPLOYEE.NAME, DEPARTMENT.DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.ID = DEPARTMENT.ID;

The view DEPT_VIEW will have the below result.

SELECT * FROM DEPT_VIEW;

Different view Operations in SQL Views

Below are various view operations are as follows:

Update in View

Though a view can be updated, we need to keep a few conditions in the notice. Such as, while updating a view the select statement should not contain a DISTINCT keyword, set functions, order by clause, Group By or Having, sub-queries, etc. Also, the FROM clause should not contain multiple tables. In addition to the above, the view should have NOT NULL values if it needs to be updated. So when we want to update the view EMPLOYEE_VW keeping the above points in focus, the table EMPLOYEE will be updated.

CREATE OR REPLACE VIEW statement is used to add or remove fields from a view.

SYNTAX for Update

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2....
FROM table_name
WHERE [condition];

Let us update the view DEPT_VIEW as below to add the location column.

CREATE OR REPLACE VIEW DEPT_VIEW AS
SELECT EMPLOYEE.ID, EMPLOYEE.NAME, DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.ID = DEPARTMENT.ID;

SELECT * FROM EMPLOYEE;

From the above query, we will get the below result.

INSERTING into VIEW

Syntax to insert into a view

INSERT INTO view_name(column1, column 2, column3,....) VALUES(value1, value2, value3,...);

A row can be inserted to view EMPLOYEE_VW by using insert into a statement as below:

INSERT INTO EMPLOYEE_VW (NAME, AGE, SALARY) VALUES(‘RAM’, ‘24’, ‘27000.00’);

After insertion, we can see the result by the below select query.

SELECT * FROM EMPLOYEE_VW;

DELETING FROM A VIEW

Deleting a row from a view deletes the row from the table on which the view was created.

The syntax for deleting from a view

DELETE FROM view_name WHERE [condition];

We can delete the row from view as below:

DELETE FROM EMPLOYEE_VW WHERE NAME = ‘MALAY’;

After deletion, the result can be displayed by the below query.

SELECT * FROM EMPLOYEE_VW;

DROPPING A VIEW

The views can be dropped by using the below syntax:

DROP VIEW view_name;

If we want to delete the view EMPLOYEE_VW, it can be deleted as below:

DROP VIEW EMPLOYEE_VW;

Advantages and Disadvantages of  SQL Views

Below are some pros and cons as follows:

Advantages

Below are the advantages of using views:

• If we need to maintain any sensitive information by providing limited access to the users, views are used for that purpose. Views are used to only display the required data to the users by keeping sensitive data safe.
• As a database view is associated with many tables upon which the view is created, it simplifies the complexity of the query.
• The view is used to hide the complexity of the underlying tables used in a database from the end-users.
• Views are useful in case of re-designing the database so as not to affect any other applications using the same database.
• The data of the computed columns can be calculated very easily when we query the data from the view, as views enable computed columns.

Disadvantages

Despite the many advantages that the views offer, it still has some disadvantages stated as below:

• One of the major disadvantages of using view comes into the picture when we change the table structures frequently upon which the view is created. So when the table structures are changed, the view also needs to be changed.
• Also, the usage of view slows down the performance of the queries.

Conclusion

Views are widely used for their many advantages. They add an extra security layer to the database which is very essential for any relational database management system. Views are flexible in case of exposure of the data to the end-users by showing only the data necessary for example using read-only views to limit the privileges to the users. But also views can be disadvantageous if the underlying table structures change much frequently thereby increasing the complexity of changing the views according to the table structures.

So with many advantages in the picture, views come with a bit less recommended when the performance of the query of data is vital to the business. It depends upon us to choose the usage of view in our database by validating the business requirement properly so as to gain more advantages from views in order to increase the performance of the system.

Recommended Articles

This has been a guide to SQL Views. Here we have discussed Creating a view in SQL and Syntax to Create View along with the Advantages and Disadvantages. You can also go through our other suggested articles to learn more –

1. Oracle PL/SQL Interview Questions
2. What is MySQL Schema?
3. What is SQL Injection?
4. What is T-SQL?
5. Learn the INSERT Statement in Oracle
6. Different View Options in MySQL