UNION and UNION ALL at DB2

Union at DB2 used to join 2 or more resultset from a query. For example if we want to see list of staff who work at IT department and FA department.

This is the syntax
select column1, column2
from table_name
where….
UNION
select column1, column2
from table_name
where….

Each column1 and column2 must be match with other resultset, or it will generate an error.

This is an simple example how to using UNION in Stored Procedure

CREATE PROCEDURE SP_PAKE_UNION ( )
DYNAMIC RESULT SETS 1
————————————————————————
– SQL Stored Procedure
————————————————————————
P1: BEGIN
– Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’E21′
union
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’A00′;
– Cursor left open for client application
OPEN cursor1;
END P1

That Store Procedure will generate list of staff who work at 'A00' and 'E21' department.

UNION is differ from UNION ALL. UNION just list the DISTINCT output. It will remove any record which is redundant. If you want to see all output, just change UNION with UNION ALL

Best regards,

Deny Sutani