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