Intersect in DBMS

This operator is used when we need to display the attributes that are present in both the tables. These attributes can be from same table or from two different tables.

Suppose user has requested to see the employees who are working in both DESIGN and TEST projects. Suppose we have only one table, EMPLOYEE where we have details about their projects. Then the query to find employees who are working both the projects would be:

SELECT EMP_ID, EMP_NAME 
FROM EMPLOYEE
WHERE PROJECT_ID IN (‘DESIGN’, ‘TEST);

But above query will not give us the required result, instead it will pull all the employees in DESIGN projects and all the employees in ‘TEST’ project. But it will not display the common employees who are in both the projects. We can write complex query like below to find this result.

SELECT EMP_ID, EMP_NAME FROM EMPLOYEE
WHERE PROJECT_ID = ‘DESIGN’
AND EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE WHERE PROJECT_ID = ‘TEST’);

This will first identify the employees who are in the TEST project and then compare their IDs with the IDs who are in DESIGN project. But this is little complex query and its cost is more. In this case INTERSECT command will help to reduce the complexity of the query and the cost.

SELECT EMP_ID, EMP_NAME FROM EMPLOYEE
WHERE PROJECT_ID = ‘DESIGN’
INTERSECT
SELECT EMP_ID, EMP_NAME FROM EMPLOYEE
WHERE PROJECT_ID = ‘TEST’;

Here the query will retrieve the employee ID and name from the employee table, and compare the attributes selected from both the query to check the common ID and name. If it finds any, it will display them. Here the columns in both the query should be same – not in terms of column name, but in terms of their datatype and size. If the column names are different, it will show the names as in first query while displaying the result. The number of columns in both queries also should be same.

If employees are in two different tables, based on their project, then above query would be:

SELECT EMP_ID, EMP_NAME FROM DESIGN_EMPLOYEE – table names are different
INTERSECT
SELECT EID, ENAME FROM TESTING_EMPLOYEE– column names are different
WHERE PROJECT_ID = ‘TEST’;