Database View's

A view is a virtual table which can be created over a single or multiple tables.

Eg: CREATE VIEW view_name1 AS (SELECT col1, col2 FROM table_name WHERE col2>25)
OR
CREATE VIEW view_name2 AS (SELECT t1.col1, t1.col3, t2.col1, t2.col4 FROM table_name1 LEFT OUTER JOIN table_name2 ON t1.col2=t2.col2 )

Internals:

1. There will not be any physical existence for a view.
2. Actually only the select query which is used in creating a view will be saved under the view name (“view_name” in the above example).
3. View is a separate database object like other's (table, procedure, function, login, trigger, cursor, sequence and etc.,).
4. So, a view can be queried like table.

   Eg: SELECT colx, coly FROM view_name1

5. The above query will be thought of the below:

   Eg: SELECT colx, coly FROM (SELECT col1, col2 FROM table_name WHERE col2>25)-> “This query is taken from the creation statement of view 'view_name1' “.

Are the Views Up-datable?

1. As we already know that there is no physical existence of view and we have already seen that, data is retrieved from original underlying table when the view is queried.
2. So, if we update a view then the underlying table should be updated. And the update operation should get executed without any problems.
3. Hold on..!
4. Yes, view are up-datable. But only when they are created on a single table as in 'view_name1'.
5. Hmm. Why so ???
6. See the creation statement of 'view_name2'. This view is created on joining two tables. In this example in particular, all the column values of the table 'table_name2' will be displayed as NULL's in the result set except the values which satisfy the join condition (ON t1.col2=t2.col2). But the original table 'table_name2' will not be having NULL values in those positions of the result set.
7. For this reason views which are created from multiple tables cannot be updated.

Usage:

1. Views are created to grant limited access to the users on the database tables. A security measure.
2. It will be very efficient to retrieve the actual desired data rather than the entire table. This can be done by creating a view on the desired data and then retrieving that data from the view. A performance measure.