Using the SELECT Statement

Go Up to Creating Views (Data Definition Guide)

The SELECT statement specifies the selection criteria for the rows to be included in the view. SELECT does the following:

• Lists the columns to be included from the base table. When SELECT * is used rather than a column list, the view contains all columns from the base table, and displays them in the order in which they appear in the base table. The following example creates a view, MY_VIEW, that contains all of the columns in the EMPLOYEE table:

CREATE VIEW MY_VIEW AS
 SELECT * FROM EMPLOYEE;

• Identifies the source tables in the FROM clause. In the MY_VIEW example, EMPLOYEE is the source table.
• Specifies, if needed, row selection conditions in a WHERE clause. In the next example, only the employees that work in the USA are included in the view:

CREATE VIEW USA_EMPLOYEES AS
 SELECT * FROM EMPLOYEE
 WHERE JOB_COUNTRY = 'USA';

• If WITH CHECK OPTION is specified, it prevents INSERT or UPDATE operations on an otherwise update-able view, if the operation violates the search condition specified in the WHERE clause. For more information about using this option, see Using WITH CHECK OPTION. For an explanation of views that can be updated, see Types of Views: Read-only and Update-able.

Important: The SELECT statement used to create a view cannot include an ORDER BY clause.