Difference between Simple and Complex View in SQL

A View in SQL as a logical subset of data from one or more tables. Views are used to restrict data access. A View contains no data of its own but it is like a window through which data from tables can be viewed or changed. The table on which a View is based is called BASE Tables. 

There are 2 types of Views in SQL: Simple View and Complex View. Simple views can only contain a single base table. Complex views can be constructed on more than one base table. In particular, complex views can contain: join conditions, a group by clause, order by clause. 

Working of views:

• When call view in SQL query it refers to database and finds definition of views which is already stored in database.
• Then the DBMS convert this call of view into equal request on the base tables of the view and carries out the operations written in view definition and returns result set to query from which view is called.

The key differences between Simple and Complex types of Views are as follows:

S. No.	Simple View	Complex View
1.	Contains only one single base table or is created from only one table.	Contains more than one base table or is created from more than one table.
2.	We cannot use group functions like MAX(), COUNT(), etc.	We can use group functions.
3.	Does not contain groups of data.	It can contain groups of data.
4.	DML operations could be performed through a simple view.	DML operations could not always be performed through a complex view.
5.	INSERT, DELETE and UPDATE are directly possible on a simple view.	We cannot apply INSERT, DELETE and UPDATE on complex view directly.
6.	Simple view does not contain group by, distinct, pseudocolumn like rownum, columns defined by expressions.	It can contain group by, distinct, pseudocolumn like rownum, columns defined by expressions.
7.	Does not include NOT NULL columns from base tables.	NOT NULL columns that are not selected by simple view can be included in complex view.
8.	In simple view, no need to apply major associations because of only one table.	In complex view, because of multiple tables involved general associations required to be applied such as join condition, group by or a order by clause.
9.	Example: CREATE VIEW Employee AS SELECT Empid, Empname FROM Employee WHERE Empid = ‘030314’;	Example: CREATE VIEW EmployeeByDepartment AS SELECT e.emp_id, d.dept_id, e.emp_name FROM Employee e, Department d WHERE e.dept_id=d.dept_id;