Using INTERSECT and EXCEPT in DB2

Both DB2 for z/OS and DB2 for LUW support the INTERSECT and EXCEPT operations in SQL SELECT statements. These two set operations can be used to simplify some SQL statements. They both are similar to UNION in the way in which they work and are coded. But there a differences.

INTERSECT is used to match result sets between two tables. If the data is the same in both results sets, it passes through. When INTERSECT ALL is specified, the result consists of all rows that are in both results sets. If INTERSECT is specified without the ALL option, the duplicates will be removed from the results. For example, the following SQL will show all customers in the USA who are also employees (with no duplicates):

SELECT last_name, first_name, cust_num
FROM CUST
WHERE country = 'USA'
INTERSECT
SELECT last_name, first_name, emp_num
FROM EMP
WHERE country = 'USA';

EXCEPT, on the other hand, combines non-matching rows from two result tables. Some other DBMS implementations refer to this as the MINUS operation. When EXCEPT ALL is specified, the result consists of all rows from the first result table that do not have a corresponding row in the second and any duplicate rows are kept. If EXCEPT is specified without the ALL option, duplicates are eliminated. As an example, the following SQL will return only those items from TABLE1 that are not also in TABLE2:

SELECT item FROM TABLE1
EXCEPT
SELECT item FROM TABLE2;

Both INTERSECT and EXCEPT make it easier to formulate SQL requests…

This post was originally published on Craig Mullins’ blog: http://db2portal.blogspot.com/2007/04/intersect-and-except-db2-9-for-zos.html