Using views

When you use views, you need to be aware of certain restrictions, both on the SELECT statements you can use to create them, and on your ability to insert into, delete from, or update them.

There are some restrictions on the SELECT statements that you can use as views. In particular, you cannot use an ORDER BY clause in the SELECT query. A characteristic of relational tables is that there is no significance to the ordering of the rows or columns, and using an ORDER BY clause would impose an order on the rows of the view. You can use the GROUP BY clause, subqueries, and joins in view definitions.

To develop a view, tune the SELECT query by itself until it provides exactly the results you need in the format you want. Once you have the SELECT query just right, you can add a phrase in front of the query to create the view. For example:

CREATE VIEW viewname AS

UPDATE, INSERT, and DELETE statements are allowed on some views, but not on others, depending on their associated SELECT statement.

You cannot update, insert into or delete from views in the following cases:

• Views containing aggregate functions, such as COUNT(*)

• Views containing a GROUP BY clause in the SELECT statement

• Views containing a UNION operation

In all these cases, there is no way to translate the UPDATE, INSERT, or DELETE into an action on the underlying tables.