Difference between Simple and Complex View in SQL

MySQLMySQLi Database

Before discussing on Simple and complex, first we should know what is View. A View is the logical virtual table created from one or more tables which can be primarily used to fetch the columns from one or more different tables at a time. On the basis of tables involved in the view we can distinguish between Simple and Complex View in SQL.

Following are the important differences between Simple and Complex View.

Sr. No.KeySimple ViewComplex View
1DefinitionSimple View in SQL is the view created by involving only single table. In other words we can say that there is only one base table in case of Simple View in SQL.On other hand, Complex View is created by involving more than one table i.e., multiple tables get projected in Complex view.
2AssociationsIn case of Simple View as only one table is in context hence no major associations need to be applied in case of this view in SQL.On other hand in case of Complex View multiple tables are in the context hence general associations need to be applied which includes join conditions, a group by clause, a order by clause.
3Group FunctionsIn Simple View, due to single table we cannot use group functions like MAX(), COUNT(), etc.On other hand in case of Complex View due to multiple tables we can use various group functions.
4Operations allowedIn Simple View, DML operations could easily be performed.However on other hand in case of Complex view DML operations could not always be performed.
5AlterationAs mentioned in above point due to DML operations INSERT, DELETE and UPDATE are directly possible.However on the other hand in case of Complex view, we cannot apply INSERT, DELETE and UPDATE.
6NULL columnsIn Simple View one cannot include NOT NULL columns from base table.However on other hand in case of Complex view, NOT NULL columns can be included in complex view.
raja
Published on 09-Jun-2020 12:31:50
Advertisements