SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-61
Using the UNION Operator
For example, consider this query:
SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPTNUM
FROM EMPLOYEE E LEFT JOIN DEPT DT
ON E.DEPTNO = DT.DEPTNO
WHERE E.LAST_NAME = "SPENCER"
AND DT.MANAGER = 23;
Table E is preserved, table DT is not. The predicates, therefore, can be evaluated as
follows:
•
The predicate E.LAST_NAME = SPENCER can be evaluated anytime after SQL
has examined a row from E.
•
The predicate DT.MANAGER = 23, however, must be evaluated only after null
augmentation is performed. This restriction exists because columns like
DT.MANAGER might contain a null value whenever a row from E is preserved.
Using the UNION Operator
SQL supports two types of union operations:
•
A UNION operation combines two tables whose respective column data types are
comparable and automatically deletes duplicate rows from the result. Thus, a
UNION of two select operations, one on TABLE1 and one on TABLE2, is the set of
all distinct rows returned from the first select (TABLE1) and the second select
(TABLE2).
•
A UNION ALL operation works the same as the UNION operation, except that
UNION ALL preserves duplicate rows in the result. If you know that no duplicates
exist in your data—or if you are willing to handle duplicate rows within your
application—the ALL option can avoid a sort operation.