UNION vs UNION ALL use in SQL Queries

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

Read DB2 blogs : Click Here IBM DB2 Manual : Click Here