Using the INTERSECT keyword
The INTERSECT keyword returns a combined result set that consists of all of the rows existing in both result sets.
Suppose that you want to find a list of employee numbers that include people in department D11 whose assignments include projects MA2112, MA2113, and AD3111.
The INTERSECT operation returns all of the employee numbers that exist in both result sets. In other words, this query returns all of the people in department D11 who are also working on projects MA2112, MA2113, and AD3111:
SELECT EMPNO
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11'
INTERSECT
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, compares it to the second interim result table, and returns the rows that exist in both tables minus any duplicate rows, and orders the results.
SELECT EMPNO
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11'
INTERSECT
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 |
---|
000150 |
000160 |
000170 |
000180 |
000190 |
000210 |