SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-62
Using the UNION Operator
Figure 1-4 shows how a union of two selects, one on LOC01 and one on LOC02, could
be useful for determining all record titles available at two different store locations.
Figure 1-5 shows how a UNION ALL of two selects, one on LOC01 and one on
LOC02, could be useful for evaluating inventory at both stores.
Note that the UNION ALL operation does not eliminate duplicate rows. Neither UNION
operation orders the results. To order results, use the ORDER BY clause, as described
in this subsection.
Figure 1-4. UNION of Two Tables
Figure 1-5. UNION ALL of Two Tables
LOC01
TITLE
AB BE Y ROAD
LET IT B E
LABEL
APPLE
APPLE
LOC02
LABEL
APPLE
CAPITOL
SELECT TITLE, LABEL, QUANTIT Y FROM LOC01
UNION SELECT TITL E, LA BEL FROM LOC02
TITLE
ABBEY ROAD
LET IT BE
RUBB ER SOUL
LA BEL
APPLE
APPLE
CAPITOL
0
12
QUANTITY
12
20
TITLE
ABBEY ROAD
RUBBER SOUL
QUANTITY
3
14
QUANTITY
12
20
14
VST0104.vsd
LOC01
TITLE
AB BE Y ROAD
LET IT B E
LABEL
APPLE
APPLE
LOC02
LABEL
APPLE
CAPITOL
SELECT TITLE, LABEL,QUANTITY FROM LOC01
UNION ALL
SELECT TITLE,LABEL,QUANTITY FROM LOC02
TITLE
ABBEY ROAD
LET IT BE
ABBEY ROAD
RUBBER SOUL
0
26
QUANTITY
12
20
QUANTITY
3
14
QUANTITY
12
20
3
14
TITLE
ABBEY ROAD
RUBBER SOUL
VST0105.vsd