Creating and using a view

 

Views provide a way to divide a table or multiple tables so that you deal with only the data that you need. A view reduces complexity and, at the same time, restricts access. You can create a view using the SQL CREATE VIEW statement.

Using the CREATE VIEW statement, you define a view on a table just as you create a new table that contains only the columns and rows that you want. When your application uses a view, it cannot access rows or columns of the table that are not included in the view. However, rows that do not match the selection criteria can still be inserted through a view if WITH CHECK OPTION is not used.

To create a view, have the appropriate authority to the tables or physical files on which the view is based.

If you did not specify column names in the view definition, the column names are the same as those for the table on which the view is based.

You can make changes to a table through a view even if the view has a different number of columns or rows than the table. For INSERT, columns in the table that are not in the view must have a default value.

You can use the view as though it were a table, even though the view is totally dependent on one or more tables for data. The view has no data of its own and therefore requires no storage for the data. Because a view is derived from a table that exists in storage, when you update the view data, you are really updating data in the table. Therefore, views are automatically kept up-to-date as the tables they depend on are updated.

• Creating a view on a single table
  You can create a view on a single table to show a subset of the data that the table contains. Compared with the original table, the view can have fewer records and fewer columns, and the columns in the view can have a different order.

• Creating a view that combines data from multiple tables
  A view that combines data from multiple tables enables you to show relevant information in multiple tables together. You can create a view that combines data from two or more tables by naming more than one table in the FROM clause.

 

Parent topic:

Getting started with SQL

 

Related concepts

WITH CHECK OPTION on a view

 

Related reference

CREATE VIEW