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
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.