Understanding Views in SQL

Tables in a SQL database may contain enormous amount of data, but they are not always in a useful format to be readily used. The volume of data must be filtered based upon some specified criteria for efficient use. Due to security reason, we might want to make public only a certain amount of data while rest might be accessible to the privileged users. The SQL DML operations are extensible and used to filter through one or more tables using complex query expressions. Leveraging the idea, we can create virtual tables from persistent base tables using SQL that would contain the exact data we need. This is the reason the SQL:2006 standard introduced the use of view tables, or views. The definition of a view or a virtual table exists as a schema object. This article introduces the concept of views in SQL, how it works and shows how it is implemented with some examples.

Introduction to SQL Views

SQL views are nothing but virtual tables reside in memory derived from one or more base tables. Virtual tables mean the tuples in views do not have physical existence and are not stored into the database. The tuples are like temporary data created as an outcome of the SQL query which typically draws filtered data from one or more base tables. As a result, there is a limitation on the type of operation that can be applied to a view table. For example, the update operation cannot be applied to all types views, but it has no limitation on applying SQL query on it.

The examples below are tested with the MySQL database. Start by creating a few tables:

my_company database:

CREATE DATABASE my_company;

CREATE TABLE Employee(
    empId INT(11) UNSIGNED CHECK (empId > 0),
    empName VARCHAR(20),
    birthDate DATE,
    address TEXT(128),
    gender VARCHAR(1),
    salary DECIMAL(15,2),
    managerId INT(11) UNSIGNED,
    deptId INT(11) UNSIGNED,
    PRIMARY KEY(empId)
);

CREATE TABLE Department(
    deptId INT(11) UNSIGNED CHECK (empId > 0),
    deptName VARCHAR(20),
    deptMgrId INT(11) UNSIGNED,
    mgrStartDate DATE,
    PRIMARY KEY(deptId)
);

CREATE TABLE Project(
    projId INT(11) UNSIGNED CHECK (empId > 0),
    projName VARCHAR(20),
    projLocation TEXT(128),
    deptId INT(11) UNSIGNED,
    PRIMARY KEY(projId)
);

CREATE TABLE EmpWorksOnProj(
    empId INT(11) UNSIGNED,
    projId INT(11) UNSIGNED,
    hoursWorked DECIMAL(4,2)
);

ALTER TABLE Employee ADD CONSTRAINT fk_emp_mgr FOREIGN KEY(managerId) REFERENCES Employee(empId);
ALTER TABLE Employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES Department(deptId);
ALTER TABLE Department ADD CONSTRAINT fk_dept_mgr FOREIGN KEY(deptMgrId) REFERENCES Employee(empId);
ALTER TABLE Project ADD CONSTRAINT fk_proj_dept FOREIGN KEY(deptId) REFERENCES Department(deptId);

The views can be thought of as a reference table, and we can use it as frequently as we want although it may not exist physically. For example, we may frequently need to refer to the my_company database and find Employee and Project information. Note that there is many to many relationship between Employee and Project as one person can work on many project and also one project has many employees. Therefore, instead of specifying the join of three tables: Employee, EmpWorksOnProj, and Project every time we need a collaborative information and issue a query, we define a view that is specified as the outcome of the join among these tables. The view forms the virtual table created from the result of the query. The advantage is that the query now can retrieve from single resultant table rather than having to retrieve from three joined tables. The collection of tables: Employee, Project, Department etc. thus form the base tables or defining table of the view.

Let's create some views based upon the schema given above.

CREATE VIEW V1
AS
SELECT
   empName, projName, hoursWorked
FROM
   Employee, Project, EmpWorksOnProj
WHERE
   Employee.empId=EmpWorksOnProj.empId
AND
   Project.projId=EmpWorksOnProj.projId;

The way to specify SQL queries on view or virtual table is same as specifying queries involving base tables. You can use SQL SELECT on views to get the data as follows:

SELECT * FROM V1;

EmpName	ProjName	HoursWorked
Mickey Mouse	ClubHouse	6.50
...	...	...
Donald Duck	Farming	7.0

The following creates a second view:

CREATE VIEW V2 AS SELECT deptName, COUNT(*), SUM(salary) FROM Department, Employee WHERE Employee.deptId=Department.deptId GROUP BY deptName;

The SQL SELECT results in

SELECT * FROM V1;

EmpName	COUNT(*)	SUM(salary)
Music	5	56000.00
...	...	...
Drama	2	25400.00

Note that in view V1 the attribute names are derived from the base table. In V2 new attribute names are explicitly specified using one to one correspondence between the specified attributes of CREATE VIEW clause and those specified in the SELECT clause. The SELECT clause with the view is decisive of the definition of the view.

The information on view is always supposed to be up to date. That means it must always reflect the changes made on the base tables on which it is defined. This is interesting, because it means that the view is not actually materialized at the time of defining it, but later when a query is specified on it. The database management system at the background is responsible for keeping the view up to date.

UPDATE, INSERT and DELETE on views

In SQL, it is possible to create updatable views that can be used to change existing data or insert new rows into the view which in turn inserts or modifies the record in the base table. A view is updatable or not is determined by SELECT statement defined within the view definition. There is no special clause to designate a view to be updatable. Typically, the view definition must be simple and must not contain any aggregate functions such as SUM, AVG, MAX, MIN, COUNT. Any sort of grouping or DISTINCT or JOIN clause also makes view not updatable. Refer to the relevant database manual of the specific RDBMS for what makes a view non-updatable.

Let’s create a view that is updatable:

CREATE VIEW v3_ch_dept_name

AS

SELECT

   deptId, deptName, deptMgrId, mgrStartDate

FROM

   Department;

The SELECT query on view:

SELECT * FROM v3_ch_dept_name;

DeptId	DeptNama	DeptMgrId	MgrStartDate
Music	5	56000.00	2020-01-01
...	...	...	...
Drama	2	25400.00	2018-03-05

Now update the view by changing the department name (deptName).

UPDATE

   v3_ch_dept_name

SET

   deptName = 'Security'

WHERE

   deptId = 5;

A row can be inserted in the view as follows:

INSERT

   INTO v3_ch_dept_name

VALUES (7,'Logistics',666884444,'1982-07-07');

Also we can DELETE a row from the view as follows:

DELETE FROM v3_ch_dept_name WHERE deptId = 7;

In MySQL, you can easily find the views in a database that are updatable or not using following SELECT command.

SELECT

   table_name

FROM

   information_schema.views

WHERE

   is_updatable like 'YES'

AND

   table_schema like 'my_company';

DROP views from the database

A view can always be disposed of with DROP VIEW <view_name> command.

DROP VIEW V1;

Note that when we execute the drop view command it removes the view definition. The underlying data stored in the base tables from which this view is derived remains unchanged. A view once dropped can be recreated with the same name.

The ALTER VIEW statement

Views are generally unalterable according to SQL:2006 standard, that means the ALTER VIEW statement does not work with views. However, there are RDBMS's like MySQL or SQL Server that supports this kind of statement. The Oracle believes in dropping the view first then recreating it rather than altering it. Therefore, the functionalities supported on views by RDBMS's vary from product to product.