Section 9.6. The UNION, INTERSECT, and EXCEPT Operators

9.6. The UNION, INTERSECT, and EXCEPT Operators

UNION, INTERSECT, and EXCEPT are operators that can be used to obtain the union, intersection, and difference between fullselect, subselect, or values-clause. Figure 9.31 shows the syntax diagram of the UNION, INTERSECT, and EXCEPT operators.

Figure 9.31. Syntax diagram of the UNION, INTERSECT, and EXCEPT operators

 >>-+-subselect---------+---------------------------------------->    +-(fullselect)------+    '-| values-clause |-'    .----------------------------------------------.    V                                              | >----+------------------------------------------+-+------------->      '-+-UNION---------+--+-subselect---------+-'        +-UNION ALL-----+  +-(fullselect)------+        +-EXCEPT--------+  '-| values-clause |-'        +-EXCEPT ALL----+        +-INTERSECT-----+        '-INTERSECT ALL-' >--+-----------------+--+--------------------+-----------------><    '-order-by-clause-'  '-fetch-first-clause-' 

9.6.1. The UNION and UNION ALL Operators

A UNION operation combines two sets of columns and removes duplicate rows. Specifying UNION ALL gives the same result as the UNION operation, but it also includes the duplicate rows. Consider the two result tables, R1 and R2, in Figure 9.32.

Figure 9.32. R1 and R2 result tables

 R1                R2 ------------      ----------- Apple             Apple Apple             Apple Apple             Banana Banana            Banana Banana            Banana Cranberry         Cranberry Cranberry         Mango Cranberry Orange 

Figure 9.33 shows the results of the UNION and UNION ALL operations on the two tables illustrated in Figure 9.32. As you can see, the UNION operator removes duplicates.

Figure 9.33. Examples of UNION and UNION ALL

 SELECT R1 FROM R1 UNION SELECT R2 FROM R2 ORDER BY 1 R1 UNION R2 ------------------ Apple Banana Cranberry Mango Orange SELECT R1 FROM R1 UNION ALL SELECT R2 FROM R2 ORDER BY 1 R1 UNION ALL R2 ------------------------ Apple Apple Apple Apple Apple Banana Banana Banana Banana Banana Cranberry Cranberry Cranberry Cranberry Mango Orange 

9.6.2. The INTERSECT and INTERSECT ALL Operators

An INTERSECT operation retrieves the matching set of distinct values from two columns; INTERSECT ALL returns the set of matching rows. The examples in Figure 9.34 use tables R1 and R2 from Figure 9.32.

Figure 9.34. Examples of INTERSECT and INTERSECT ALL

 SELECT R1 FROM R1 INTERSECT SELECT R2 FROM R2 ORDER BY 1 R1 INTERSECT R2 ------------------ Apple Banana Cranberry SELECT R1 FROM R1 INTERSECT ALL SELECT R2 FROM R2 ORDER BY 1 R1 INTERSECT ALL R2 ------------------------ Apple Apple Banana Banana Cranberry 

9.6.3. The EXCEPT and EXCEPT ALL Operators

An EXCEPT operation retrieves the set of distinct values that exist in the first table but not in the second table. EXCEPT ALL returns the set of rows that exist only in the first table. The examples in Figure 9.35 use tables R1 and R2 from Figure 9.32.

Figure 9.35. Examples of EXCEPT and EXCEPT ALL

 SELECT R1 FROM R1 INTERSECT SELECT R2 FROM R2 ORDER BY 1 R1 INTERSECT R2 ------------------ Mango SELECT R1 FROM R1 INTERSECT ALL SELECT R2 FROM R2 ORDER BY 1 R1 INTERSECT ALL R2 ------------------------ Apple Cranberry Cranberry Mango 

NOTE

The file Command_and_SQL_Examples.pdf on the CD-ROM accompanying this book lists all of the SQL statements and DB2 commands with examples.