A UNION operation combines two sets of columns and removes duplicates. The UNION ALL expression does the same but does not remove the duplicates. When DB2 encounters the keyword, it processes each select / sub-select to form an interim result table, then it combines the interim result table and deletes duplicate rows to form a combined result table working similarly as a JOIN. To use this clause, each SELECT statement must have
- The same number of columns selected.
- The same number of column expressions.
- The same data type and have them in the same order.
UNION – UNION ALL Syntax
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION / UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
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 sub-select is evaluated first, followed, from left to right, by the other parts of the statement.
Recursive SQL requires that there be a UNION ALL phrase between the two main parts of the statement. As UNION ALL, unlike the UNION, allows for duplicate output rows which is what often comes out of recursive processing. Let’s consider below example
Table R1 Table R2
R1 R2
Abhi Abhi
Abhi Abhi
Abhi Baby
Baby Baby
Baby Baby
Cat Cat
Cat Dan
Cat
Elie
SELECT R1
FROM R1
UNION / UNION ALL
SELECT R2
FROM R2
ORDER BY 1;
UNION UNION ALL
Abhi Abhi
Baby Abhi
Cat Abhi
Dan Abhi
Elie Abhi
Baby
Baby
Baby
Baby
Baby
Cat
Cat
Cat
Cat
Dan
Elie
When you use :
- Any ORDER BY clause must appear after the last sub-select that is part of the union. In this example, the results are sequenced on the basis of the first selected column, R1. The ORDER BY clause specifies that the combined result table is to be in a collated sequence. ORDER BY is not allowed in a view.
- A name may be specified on the ORDER BY clause if the result columns are named. A result column is named if the corresponding columns in each of the unioned select-statements have the same name. An AS clause can be used to assign a name to columns in the select list.
SELECT A + B AS X … UNION SELECT X … ORDER BY X
If the result columns are unnamed, use a positive integer to order the result. The number refers to the position of the expression in the list of expressions you include in your sub-selects.
SELECT A + B … UNION SELECT X … ORDER BY 1