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 its like window through which data from tables can be viewed or changed. The table on which a View is based are 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, a order by clause.
The key differences between these types of Views are:
|Simple View||Complex View|
|Contains only one single base table or is created from only one table.||Contains more than one base tables or is created from more than one tables.|
|We cannot use group functions like MAX(), COUNT(), etc.||We can use group functions.|
|Does not contain groups of data.||It can contain groups of data.|
|DML operations could be performed through a simple view.||DML operations could not always be performed through a complex view.|
|INSERT, DELETE and UPDATE are directly possible on a simple view.||We cannot apply INSERT, DELETE and UPDATE on complex view directly.|
|Simple view does not contain group by, distinct, pseudocolumn like rownum, columns defiend by expressions.||It can contain group by, distinct, pseudocolumn like rownum, columns defiend by expressions.|
|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.|
View – orafaq
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.