A query expression is an expression involving one or more query specifications connected using the UNION operator.
Syntax:
|
ALL |
Duplicate rows originating from different UNION operands are to be retained. |
|
UNION |
A diadic operator which takes specifications of resultant tables as its operands. These operands can be query specifications or deeper nested query expressions. |
|
query_specification |
The basic element of a query expression. It specifies a resultant table derived from a query. See Query Specification. |
A query expression specifies a resultant table created from the possible UNION of several resultant tables as specified in corresponding QUERY specifications. A query expression can consist of a single query specification. You can use the UNION operator to add subsequent resultant tables to this initial query specification to produce a larger result.
The result of the UNION operation is a resultant table. The resultant table contains all rows from both operands after eliminating duplicate rows.
The result of a UNION operation with two base tables is a table that contains all rows belonging to either or both the operands.
If the expression does not contains ALL you do not have to include DISTINCT in any of the query specifications because duplicates are automatically eliminated.
If the expression does contain ALL, duplicated rows are retained. In that case, including DISTINCT in the query specification will affect the resultant table.
When ALL is either always specified with each UNION operator or never specified within the query expression, you do not have to use parentheses. If the ALL qualifier is only partially used, then the order of evaluation determines the final result; in that case, you may need to use parentheses.
Query expressions specified within parentheses are evaluated first; following that evaluation, expressions are evaluated from left to right.
When a UNION operator is specified, then the columns of the resultant table do not have derived column labels.
The two operands must be UNION-compatible; the derived column lists of the two operands must be of the same format.
Each derived column list must have the same number of derived columns and each derived column must be of the same data type as its corresponding derived column in the derived column list of the other operand.
None.
None.
Example:
The following example selects all cruise IDs for any contracts that require final payment or start before the 30th December 1991:
SELECT cruise_id
FROM cruise
WHERE start_date < = 19911230
UNION
SELECT id_cruise
FROM contract
WHERE date_payment < = 19911230;