| The UNION operation lets you combine the results of two or more different SQL statements into one answer set. You can combine many different tables or SQL statements using the UNION (or UNION ALL) operator; the only restriction is that every table or SQL statement must have the same type, number, and order of columns . The term used to describe this is that they must be UNION-compatible. Suppose you wanted to combine the minimum and maximum score for each DB2 Certification program exam on different output rows, and add a string constant that indicates which values are the maximum and minimum. SELECT number,'Minimum:', MIN(score) FROM test_taken GROUP BY number UNION SELECT number,'Maximum:', MAX(score) FROM test_taken GROUP BY number ORDER BY number,2 The UNION operator shows you the results of two or more separate queries as a single result. In our example, the first query calculates the minimum score of the TEST_TAKEN table. Then the second query calculates the maximum score value. Both queries have the same type, order, and number of columns. In the above example, the two SQL statements are very similar. However, you can combine very different queries using the UNION operator. Just remember the restriction about the resulting rows. The UNION operator removes duplicate rows from the resulting set. However, there will be times when you'll need to list all the rows processed by your SQL statements or when duplicates are not possible. SQL provides you with an operator clause that allows you to keep all the rows involved in a UNION operation. This is the ALL clause. Since the previous example can never produce duplicate rows, the ALL clause can be added to the UNION: SELECT number,'Minimum:', MIN(score) FROM test_taken GROUP BY number UNION ALL SELECT number,'Maximum:', MAX(score) FROM test_taken GROUP BY number ORDER BY number,2 NOTE  | Always try to code a UNION ALL rather than UNION. Code a UNION only when duplicates are possible and are not desired. The UNION ALL offers better performance, since a UNION will always invoke a sort to remove duplicates, and a UNION ALL will not. However, you can't always substitute a UNION with a UNION ALL. |
NOTE | | A UNION (and UNION ALL) can be used between any combination of subselects and fullselects, and in subqueries, views, and table expressions. |
|
