The INTERSECT keyword returns a combined result set that consists of all of the rows that exist in both result sets.
Suppose you want to find a list of employee numbers that includes:
People in department D11
People whose assignments include projects MA2112, MA2113, and AD3111
INTERSECT returns the 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.
To do this, specify:
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
EXCEPT returns any distinct values from the left query that are not also found on the right query.
Keywords : Intersect and Except in Sql Server
|