Specifying the UNION ALL keyword
If you want to keep duplicates in the result of a UNION operation, specify the UNION ALL keyword instead of just UNION.
This topic uses the same steps and example as Using the UNION keyword to combine subselects.
Step 3. SQL combines two interim result tables:
SELECT EMPNO
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11'
UNION ALL
SELECT EMPNO
FROM CORPDATA.EMPPROJACT
WHERE PROJNO='MA2112' OR
PROJNO= 'MA2113' OR
PROJNO= 'AD3111'
ORDER BY EMPNO
The query returns an ordered result table that includes duplicates.
EMPNO |
---|
000060 |
000150 |
000150 |
000150 |
000160 |
000160 |
000170 |
000170 |
000170 |
000170 |
000180 |
000180 |
000190 |
000190 |
000190 |
000200 |
000210 |
000210 |
000210 |
000220 |
000230 |
000230 |
000230 |
000230 |
000230 |
000240 |
000240 |
200170 |
200220 |
The UNION ALL operation is associative, for example:
(SELECT PROJNO FROM CORPDATA.PROJECT
UNION ALL
SELECT PROJNO FROM CORPDATA.PROJECT)
UNION ALL
SELECT PROJNO FROM CORPDATA.EMPPROJACT
This statement can also be written as:
SELECT PROJNO FROM CORPDATA.PROJECT
UNION ALL
(SELECT PROJNO FROM CORPDATA.PROJECT
UNION ALL
SELECT PROJNO FROM CORPDATA.EMPPROJACT)
When you include the UNION ALL in the same SQL statement as a UNION operator, however, the result of the operation depends on the order of evaluation. Where there are no parentheses, evaluation is from left to right. Where parentheses are included, the parenthesized subselect is evaluated first, followed, from left to right, by the other parts of the statement.