Tuesday, April 03, 2007

INTERSECT and EXCEPT [DB2 9 for z/OS]

With this blog entry I am introducing a new series in which I will briefly blog about the new feature of DB2 9 for z/OS. Today's entry will cover the new INTERSECT and EXCEPT keywords.

DB2 for Linux, Unix, and Windows has supported INTERSECT and EXCEPT in SQL SELECT statements for quite some time now, and with V9 the z/OS platform catches up. These two set operations can be used to simplify some SQL statements. Think of them as being similar to the UNION operation.

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 result 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...

3 comments:

Tulu said...

how can we mimick the results of an EXCEPT statement in an earlier version of DB2

M4io said...

select colA1,colA2 from stmtA
except
select colB1,colB2 from stmtB

=

select colA1,colA2 from stmtA
where not exists
(select * from stmtB
where (colA1,colA2) = (colB1,colB2))

=

select colA1,colA2 from stmtA
where (colA1,colA2) not in
(select colB1,colB2 from stmtB)

priyanka said...

In one of my projects, i have used the INTERSECT command to get: Lets say no of customers who stays in different geographies. For testing purpose, DB2 query was created consisting of 20 INTERSECTS. This has resulted in DB2 error code as Query very complex and didnt get executed. Is there any other command which would improve the performance of the above query.