SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-27
GROUP BY Clause, HAVING Clause, and the
UNION Operator

This example on SELECT 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 that it is a part of (unlike the ORDER BY
clause, which is 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 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, however, the
result depends on the order of evaluation. A parenthesized union of SELECT
statements is evaluated first, from left to right, followed by a left-to-right evaluation of
the remaining union of SELECT statements.
Examples—SELECT

This SQLCI example retrieves information from the EMPLOYEE table for
employees with a job code greater than 500, and employees in departments with
numbers less than or equal to 3000, displaying the results in ascending order by
job code. No locks are held while the query is processed.
>> SET LIST_COUNT 3;
>> SELECT JOBCODE, DEPTNUM, FIRST_NAME, LAST_NAME, SALARY
+> FROM PERSNL.EMPLOYEE
+> WHERE JOBCODE > 500 AND DEPTNUM <= 3000
+> ORDER BY JOBCODE
+> BROWSE ACCESS;
JOBCODE DEPTNUM FIRST_NAME LAST_NAME SALARY
------- ------- --------------- ----------- ----------
600 1500 JIMMY SCHNEIDER 26000.00
600 1500 JONATHAN MITCHELL 32000.00
900 1000 SUE CRAMER 19000.00
S> LIST NEXT 2;
900 1500 SUSAN CHAPMAN 17000.00
900 2000 BILL WINN 32000.00
S> CANCEL;