Difference between Simple and Complex View in SQL
Prerequisite – SQL | Views
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.
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 | 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; |