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.

 

Parent topic:

Using the UNION keyword to combine subselects