INTERSECT in SQL

INTERSECT in SQL

Overview: The INTERSECT operator in SQL is used to retrieve the common records between two or more SELECT statements. It returns only the rows that are present in all the result sets of the SELECT statements.

Syntax: The basic syntax for using the INTERSECT operator is as follows:

sql
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

Example: Suppose we have two tables, "Employees" and "Managers," and we want to retrieve the employees who are also managers. The tables have the following structure:

Table: Employees

emp_id	emp_name
1	John
2	Mary
3	David
4	Sarah

Table: Managers

manager_id	manager_name
2	Mary
3	David
5	Michael

To find the employees who are also managers, we can use the INTERSECT operator:

sql
SELECT emp_id, emp_name
FROM Employees
INTERSECT
SELECT manager_id, manager_name
FROM Managers;

Explanation: In the above example, we first select the columns "emp_id" and "emp_name" from the "Employees" table. Then, we use the INTERSECT operator to find the common records between the two SELECT statements.

Next, we select the columns "manager_id" and "manager_name" from the "Managers" table. The INTERSECT operator compares the result sets of both SELECT statements and returns only the rows that exist in both result sets.

In this case, the result would be:

emp_id	emp_name
2	Mary
3	David

These are the employees who are also managers based on the common records present in both tables.

Note: The INTERSECT operator only considers the distinct rows in each SELECT statement. If there are duplicate rows within a single SELECT statement, the INTERSECT operator will eliminate the duplicates before comparing the result sets.