The UNION operation lets you combine the results of two or more SQL statements into one answer set. You can combine many tables or SQL statements by 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 that you want to combine the minimum and maximum scores 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. The second query calculates the maximum score value. Both queries have the same type, order, and number of columns.
In the previous example, the two SQL statements are very similar. However, you can combine very different queries by using the UNION operator, but remember the restriction about the resulting rows.
The UNION operator removes duplicate rows from the resulting set. However, you will sometimes 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. Because 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
Always try to code a UNION ALL rather than a UNION. Code a UNION only when duplicates are possible and are not desired. The UNION ALL offers better performance, as 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.
A UNION (and UNION ALL) can be used between any combination of subselects and fullselects and in subqueries, views, and table expressions.
The ability to have a UNION within a view has provided an alternative for partitioned table designs that have grown beyond the DB2 physical limit of 128TB or for large partitioned table spaces that have become too much of an obstacle for 24/7 availability. A UNION ALL in a view would appear to applications using the view that it is a single table. This technique has many benefits, such as independence of the tables and no need for large NPIs.
However, some coding techniques have to be applied in order to best use this feature. Suppose that you have the following design: five independent tables (TC1TC5), each holding data for different TEST_CENTERs. A view (TESTCTR_VIEW) could be developed to UNION all five tables together to appear as one larger table. The view definition follows:
CREATE TESTCTR_VIEW(TCID) SELECT TCID FROM TC1 WHERE TCID = 1 UNION ALL SELECT TCID FROM TC2 WHERE TCID = 2 UNION ALL SELECT TCID FROM TC3 WHERE TCID = 3 UNION ALL SELECT TCID FROM TC4 WHERE TCID = 4 UNION ALL SELECT TCID FROM TC5 WHERE TCID = 5
Queries written against the TESTCTR_VIEW will need to repeat the predicate defined in the view in order for DB2 to use subquery pruning. Subquery pruning applies to UNION ALL queries and requires redundant predicates to be coded in view definition in order to compare distributed predicates with defined predicates in the view definition; then DB2 removes unnecessary query blocks at bind time or runtime. This limits the number of tables that are accessed to satisfy the query.
Using the preceding view definition, table TC1 contains values such that TCID = 1, table TC2 contains values such that TCID = 2, and so on. The predicate TCID BETWEEN 3 AND 4 will be distributed to each subselect in the view; therefore, only the necessary tables will be accessed. Figure 6-1 shows how the predicate in the select works with the tables in the view.
Figure 6-1. Predicates for subquery pruning
After the predicates are distributed, DB2 can then prune unnecessary query blocks. Unnecessary query blocks are those in which the distributed predicates, combined with the original view definition predicates, evaluate to FALSE. The original view definition must contain the predicates to allow pruning to occur, even if redundant.