Difference between UNION and UNION ALL in DB2

Answer

When DB2 encounters the UNION keyword, it processes each select / subselect to form an interim result table, then it combines the interim result table and deletes duplicate rows to form a combined result table working similar as a JOIN.
If the option is to keep the duplicate values, then the UNION ALL keyword should be used.

Note: When including 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.

To test the usage of both keywords, use the example below:

--- To create the objects:

db2 "create table Table1 (A CHAR, B CHAR, C CHAR)"
db2 "create table Table2 (A CHAR, B CHAR, C CHAR)"

db2 "insert into Table1 values( '0' , '0' , '0')"
db2 "insert into Table1 values( '0' , '0' , '0')"
db2 "insert into Table1 values( '1' , '1' , '1')"
db2 "insert into Table1 values( '1' , '1' , '1')"
db2 "insert into Table1 values( '2' , '2' , '2')"
db2 "insert into Table1 values( '2' , '2' , '2')"

db2 "insert into Table2 values( '0' , '0' , '0')"
db2 "insert into Table2 values( '1' , '1' , '1')"
db2 "insert into Table2 values( '2' , '2' , '2')"
db2 "insert into Table2 values( '3' , '3' , '3')"
db2 "insert into Table2 values( '4' , '4' , '4')"

db2 "SELECT * FROM Table1"
db2 "SELECT * FROM Table2"

db2 "SELECT a.A, a.B, a.C FROM Table1 AS a UNION ALL SELECT b.A, b.B, b.C FROM Table2 as b"

--- Results expected:

$ db2 "SELECT * FROM Table1"

A B C
- - -
0 0 0
0 0 0
1 1 1
1 1 1
2 2 2
2 2 2

6 record(s) selected.


$ db2 "SELECT * FROM Table2"

A B C
- - -
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4

5 record(s) selected.


$ db2 "SELECT a.A, a.B, a.C FROM Table1 AS a UNION ALL SELECT b.A, b.B, b.C FROM Table2 as b"

A B C
- - -
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
0 0 0
0 0 0
1 1 1
1 1 1
2 2 2
2 2 2

11 record(s) selected.


$ db2 "SELECT a.A, a.B, a.C FROM Table1 AS a UNION SELECT b.A, b.B, b.C FROM Table2 as b"

A B C
- - -
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4

5 record(s) selected.