Union operation - IBM Mainframe

The union operation combines two sets of rows into a single set composed of all the rows in either or both of the two original sets provided the two original sets are union compatible, (whereas a join combines two sets of columns into a single set). For union compatibility:

  • The two sets must contain the same number of columns.
  • Each column of the first set must be either the same data type as the corresponding column of the second set or convertible to the same data type as corresponding column of the second set.

In theory, the union of two sets cannot contain any duplicates. But DB2 provides the provision of retaining or eliminating the duplicates. The UNION verb eliminates duplicates but UNION ALL retains them. Consider the following example:

  • Get the name of parts that weighs either more than 12 pounds or are supplied by Supplier S2 or both. The following SQL will achieve the required results.

Union operation

  • Duplicates are always eliminated from the results table of a UNION unless the UNION ALL operator is specified. But in some cases it improves performance to use UNION ALL instead of UNION. These are case, where we know for sure that there will not be any duplicates and any attempt by the system to eliminate the duplicates will simply increase the reducing the performance
  • Any number of SELECTS can be UNIONed together.
  • Parentheses can be used to force a particular order of evaluation if multiple UNIONs are involved. For example, A UNION ALL (B UNION C) and (A UNION ALL B) UNION C are not equivalent. But if all the operators are either UNIONs or UNION ALLs then parentheses are unnecessary.
  • Any ORDER BY clause in the query must appear as part of the final SELECT only and must identify ordering columns by their ordinal position or number and not by name.

Outer Join

When tables are joined, rows, which contain matching values in the join predicates, are returned. Sometimes, you may want both matching and no-matching rows returned for the tables that are being joined. This kind of an operation is known as an 'outer join', but DB2 does not support the outer joins explicitly, even though you can achieve the results in a round about way by combining a join and a correlated subquery with a UNION verb. For example, consider the following two tables:

Outer Join

Now, you want to get the supplier number, name, status and city together with the part numbers for all parts supplied by that supplier. Moreover, if a supplier does not supply any parts, you want that information also included in the result by indicating it with a blank part number. You can achieve this by using the following SQL.

Outer Join

The first 4 rows are the result of the natural join of' S' and 'SP' over supplier numbers. The final row. which contains the Information about the supplier who does not supply any parts, is the result of the correlated subquery. The overall result is the outer join of' S' and 'SP' over S#. UNION ALL can always be used in constructing an outer join, since there are no duplicates to be eliminated and UNION ALL will improve performance.

Very often outer joins are required by applications and since DB2 and for that matter most of the relational database management systems, does not support outer joins, it is necessary to learn some work­around like the above example.

Outer join is a top priority requirement for query users and for many application programmers. DB2 has an SQL join function that lets you combine data from two operand tables into a single result table. Releases prior to DB2 version 4 provided an inner join function in which the result table contains only data from rows, which have matching values in the join columns of the operand tables. This function is limited.

If you want a list of all your products and the orders placed by customers during a sales period, including products for which there were no orders, the inner join will not give the desired result. To achieve the desired result with existing function you would have to combine an inner join with a UNION and a NOT EXISTS predicate as we have seen in the above example.

New SQL enhancements to Version 4 let you perform join operations on rows of data in which the values of the join columns do not match. This type of join operation is called an outer join. The result of an outer join can include data from rows that have unmatching values as well as matching values in the join columns from all tables or specific tables.

Unlike inner join, your result table can now include the products that have no orders. This change can improve your productivity and gives you the opportunity to improve performance by using outer join rather than application workarounds.

The new outer join function lets you use the VALUE function or the new COALESCE function to construct a single result column from the non-null values of the join columns of the two-operand tables. New function supports LEFT, RIGHT, and FULL OUTER JOIN and explicit syntax for INNER JOIN consistent with ANSI/ISO SQL entry level standard of 1992.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics