Learning Visually with Examples [Electronic resources]

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.