SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-201
Considerations for UNION
You can specify an ORDER BY clause only as the last clause following the final
SELECT statement (SELECT2 in this example). The ORDER BY clause in RESULT
specifies the ordinal position of the sort column either by using an integer or by using
the column name from the select list of SELECT1.
This SELECT statement shows correct use of the ORDER BY clause:
SELECT A FROM T1 UNION SELECT B FROM T2 ORDER BY A
This SELECT statement is incorrect because the ORDER BY clause does not follow
the final SELECT statement:
SELECT A FROM T1 ORDER BY A UNION SELECT B FROM T2
This SELECT statement is also incorrect:
SELECT A FROM T1 UNION (SELECT B FROM T2 ORDER BY A)
Because the subquery (SELECT B FROM T2...) is processed first, the ORDER BY
clause does not follow the final SELECT.
GROUP BY Clause, HAVING Clause, and the UNION
Operator
In a query containing a UNION operator, the GROUP BY or HAVING clause is
associated with the SELECT statement it is a part of (unlike the ORDER BY clause,
which can be associated with the result of a UNION operation). The groups are visible
in the result table of the particular SELECT statement. The GROUP BY and HAVING
clauses cannot be used to form groups in the result of a UNION operation.
UNION ALL and Associativity
The UNION ALL operation is left associative, meaning that these two queries return
the same result:
(SELECT * FROM TABLE1 UNION ALL
SELECT * FROM TABLE2) UNION ALL SELECT * FROM TABLE3;
SELECT * FROM TABLE1 UNION ALL
(SELECT * FROM TABLE2 UNION ALL SELECT * FROM TABLE3);
If both the UNION ALL and UNION operators are present in the query, the order of
evaluation is always from left to right. A parenthesized union of SELECT statements is
evaluated first, from left to right, followed by the remaining union of SELECT
statements.
Access Modes and the UNION Operator
In a query containing the UNION operator, if you specify an access option for the
second operand before the ORDER BY clause (or if the UNION has no ORDER BY
clause) and you do not specify an option for the first operand, the first operand inherits
the session’s transaction isolation level setting. If this setting is different from the one