Some DB2 versions ago two new alternatives to UNION were introduced. They are called INTERSECT and EXCEPT. INTERSECT selects those rows that are common to both of the two SQL statements INTERSECT is written between. Even more interesting is EXCEPT which only selects the rows from the first SELECT that are NOT present in the second SELECT. This is exactly why EXCEPT can be used as a replacement for NOT EXISTS.

I will now demonstrate EXCEPT using an example. The task is to find those tables in the system catalogue that does not have any indexes defined on them. Normally you will use this SQL statement:

SELECT t.name
FROM sysibm.systables t
WHERE t.creator = 'SYSIBM'
  AND t.type = 'T'
  AND NOT EXISTS
( SELECT 0 FROM sysibm.sysindexes i
  WHERE i.tbcreator = t.creator
    AND i.tbname = t.name
)

By using EXCEPT instead the task can be solved like this:

SELECT t.name
FROM sysibm.systables t
WHERE t.creator = 'SYSIBM'
  AND t.type = 'T'
EXCEPT
SELECT i.tbname
FROM sysibm.sysindexes i
WHERE i.tbcreator = 'SYSIBM'

The result of these two SQL statements is exactly the same list of tables in the system catalogue without indexes defined on them. The difference lies completely in the way DB2 solves the task. In both cases DB2 will start by finding all tables in SYSIBM.SYSTABLES that fulfulls the conditions in the WHERE clause.

In the first example DB2 will for each matching table examine the presence of a row in SYSIBM.SYSINDEXES belonging to the table. If no such row is found the table is included in the final result. In the second example DB2 will find all indexes in SYSIBM.SYSINDEXES matching the conditions in the WHERE clause. Afterwards DB2 will remove the rows returned by the first SELECT that are equal to rows in the second SELECT.

Whether EXCEPT or NOT EXISTS is the best solution will depend on the task to be solved. I will start by coding my solution as a NOT EXISTS, but if the statement executes very slowly I will try using EXCEPT instead. Generally NOT EXISTS will require less CPU but have longer elapsed time when dealing with many rows. It is the opposite when using EXCEPT.