A view is a logical subset of one or more base tables or views where columns are chosen by naming them and rows are chosen through specified conditions relating to column values.
Views are created, for example, so that users who need not see all the data in a single table are shown only the parts of the table that interest them (restriction views). Views can also be created as a combination of a number of columns from several different tables (join views).
Operations on views are actually performed on the underlying base tables. Certain view definitions do not allow data to be changed in the view (read-only views). See Updatable Views for further details.
View names can be up to 128 characters long. Views are defined in terms of a
SELECTstatement; the result of the
SELECTstatement forms the contents of the view. There are no restrictions on which select statements that can be used in a view definition.
Creating a View
Create a restriction view on the CUSTOMERS table called CUSTOMER_DETAILS containing limited information:CREATE VIEW customer_details AS SELECT surname, forename, address_1, address_2, town, postcode, title, date_of_birth, country_code, customer_id FROM customers;
The columns in the view are named after the columns listed in the
SELECTclause, since the view definition does not include a list of column names.
Create a join view, including an outer join:CREATE VIEW product_details AS SELECT product, COALESCE(producer, ' ') AS producer, format, price, stock, reorder_level, release_date, ean_code, status, product_search, item_id, category_id, product_id, display_order, image_id FROM products NATURAL JOIN items NATURAL JOIN formats NATURAL LEFT OUTER JOIN producers;
The check option can be used in updatable view definitions to limit the data that can be inserted into the view. If a check option is specified, data which does not fulfill the definition of the view cannot be inserted into the view.CREATE VIEW swedish_customers AS SELECT * FROM customer_details WHERE country_code = 'SE' WITH CHECK OPTION;
The check option in the view definition (
WITH CHECK OPTION) means that the
COUNTRY_CODEcolumn must be set to
SEif new rows are inserted to into the view or rows are updated using the view.
If there is an instead of trigger defined for the view, the
WITH CHECK OPTIONdoes not have any effect.
Creating Views Based on Other Views
Views can be based on other views. When a view is created based upon another view or views, the original view's limitations are carried over to the new view.CREATE VIEW customer_addresses (surname, forename, recipient, address_1, address_2, town, postcode, country, salutation, customer_id) AS SELECT surname, forename, UPPER(recipient(title, forename, surname)), address_1, COALESCE(address_2, ' '), UPPER(town), UPPER(postcode), UPPER(country), salutation(title, forename, surname, date_of_birth, country_code), customer_id FROM customer_details JOIN countries ON code = country_code;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40