Summary -

In this topic, we described about the below sections -

What is View?

View is virtual table that is created on one more table. The data from more than one table can display as just like another real table. However, it is not real table and it is just a consolidation of the data that is being represented in the table format.

View consists of rows and columns that are from one or more tables. The data in the view are composed of two or more real tables data in database.

View never stores data and have no real structure like table. It always fetches the data from base tables that are used to create the view using view creation query.


Note :- A view always shows latest data from base tables. The database engine recreates the data, using the view's SQL statement, whenever user queries a view.

View Types

There are 2 types of views in SQL and those are –

  • Simple View
  • Complex View

Below are the key differences between the simple an complex views -

Simple View Complex View
Creates on one base table or is created from only one table. Creates on more than one table or is created from more than one tables.
Can't use group functions like MAX(), COUNT(), etc. Can use group functions.
Does not contain groups of data. Contain groups of data.
DML operations can perform on a simple view. DML operations may not always perform on a complex view.
INSERT, DELETE and UPDATE can apply directly on a simple view. INSERT, DELETE and UPDATE can't apply directly on complex view directly.
Does not contain GROUP BY, ORDER BY, DISTINCT etc in the CREATE VIEW. Can contain GROUP BY, ORDER BY, DISTINCT etc in the CREATE VIEW.
Does not have NOT NULL columns from base tables. Can have NOT NULL columns from base tables.

Advantages -

  • Security – The user who doesn't have access to base tables still have access to view to retrieve the information. This way, we can protect the sensitive data from unauthorized access.
  • Views can join and simplify multiple table data into single table.
  • Acts as aggregate tables.
  • Hides data complexity.
  • Easy to use for end-user
  • Occupies very less space.
  • C omplex queries are eliminated by using views.
  • Provides additional security to the data.

Disadvantages -

  • View is dependent on table.
  • When table is dropped views are get dropped or produce unpredictable results.
  • Performance get decreased if the CREATE VIEW query not coded properly.
  • Update restrictions.