Query expressions are the SQL language constructs for selecting rows and columns from base tables and views. A query expression results in a derived table containing the selected rows.
Query expressions are used, for example, to define a view or a cursor or to select a set of rows to be inserted into a table with the INSERT statement.
The UNION operator links two query expressions in such a way that the derived table contains all those rows which occur in at least one of the derived tables for the query expressions linked by the operator.
The EXCEPT operation in the query expression is comparable to the difference between two sets in set theory.
Updatable query expression
The concept of an updatable query expression is of significance for defining an updatable view or an updatable cursor. A query expression is updatable if it fulfills the following criteria:
The query expression does not contain a join expression.
Join expressions are described in the section “Join expression”.The query expression must not contain any UNION operation or EXCEPT operation.
Only column names can be specified in the SELECT list. Other elements of an expression, e.g. subqueries, function calls or literals, are not permitted. Atomic columns cannot be specified more than once. Subareas from multiple columns cannot overlap.
Only a table or updatable subquery can be specified in the FROM clause. If a table is specified, it must be a base table or an updatable view.
No subquery can occur in the WHERE clause.
The keyword DISTINCT cannot be specified.
The SELECT expression cannot include a GROUP BY or HAVING clause.