NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-27
GROUP BY Clause, HAVING Clause, and the
UNION Operator
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 the following 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
•
The following 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;