Using the EXCEPT keyword

 

The EXCEPT keyword returns the result set of the first subselect minus any matching rows from the second subselect.

Suppose that you want to find a list of employee numbers that includes people in department D11 minus those people whose assignments include projects MA2112, MA2113, and AD3111.

This query returns all of the people in department D11 who are not working on projects MA2112, MA2113, and AD3111:

SELECT EMPNO    FROM CORPDATA.EMPLOYEE    WHERE WORKDEPT = 'D11'
 EXCEPT
 SELECT EMPNO    FROM CORPDATA.EMPPROJACT    WHERE PROJNO = 'MA2112' OR
      PROJNO = 'MA2113' OR
      PROJNO = 'AD3111'
 ORDER BY EMPNO

To better understand the results from these SQL statements, imagine that SQL goes through the following process:

Step 1. SQL processes the first SELECT statement:

SELECT EMPNO      FROM CORPDATA.EMPLOYEE      WHERE WORKDEPT = 'D11'

This query returns an interim result table.

EMPNO from CORPDATA.EMPLOYEE
000060
000150
000160
000170
000180
000190
000200
000210
000220
200170
200220

Step 2. SQL processes the second SELECT statement:

SELECT EMPNO      FROM CORPDATA.EMPPROJACT      WHERE PROJNO='MA2112' OR
                 PROJNO= 'MA2113' OR
                 PROJNO= 'AD3111'

This query returns another interim result table.

EMPNO from CORPDATA.EMPPROJACT
000230
000230
000240
000230
000230
000240
000230
000150
000170
000190
000170
000190
000150
000160
000180
000170
000210
000210

Step 3. SQL takes the first interim result table, removes all of the rows that also appear in the second interim result table, removes duplicate rows, and orders the result:

SELECT EMPNO 
     FROM CORPDATA.EMPLOYEE      WHERE WORKDEPT = 'D11'

EXCEPT

SELECT EMPNO 
     FROM CORPDATA.EMPPROJACT      WHERE PROJNO='MA2112' OR
                 PROJNO= 'MA2113' OR
                 PROJNO= 'AD3111'

ORDER BY EMPNO

This query returns a combined result table with values in ascending sequence.

EMPNO
000060
000200
000220
200170
200220

 

Parent topic:

Retrieving data using the SELECT statement