INTERSECT Operator in SQL

The INTERSECT operator in SQL retrieves common records. These are the records that exist in both result sets, which come from two distinct SELECT queries. This operator is the inverse of the UNION operator. While UNION gets all unique records from both result sets, the INTERSECT operator focuses on finding the common records between them. It's conceptually simpler and has more straightforward syntax compared to operators like JOIN.

The INTERSECT operator pulls up the common records identified in both data sets of two SELECT queries. Unlike UNION, which combines all unique records from both sets, INTERSECT only returns records that are found in both sets. Let's look at the basic syntax of the INTERSECT operator:

Syntax:

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

In this syntax, "column1, column2, ..." are the columns you're selecting from both tables. Let’s take an example to illustrate this.

Example:

Table: Employees

EmployeeID	Name
1	Alice
2	Bob
3	Alice

Table: Managers

ManagerID	Name
101	David
102	Alice
103	Eve

Now, let's use the INTERSECT operator to find common records between the Employees and Managers tables:

Using INTERSECT:

SELECT EmployeeID, Name FROM Employees INTERSECT SELECT ManagerID, Name FROM Managers;

Result:

EmployeeID	Name
2	Alice

In this example, only the common record between the two tables is returned. The Name "Alice" is the only record present in both the Employees and Managers tables, so it’s the only one displayed in the result.

Real-Life Example

Imagine two companies, Company A and Company B, each having their own employee records. We want to identify the employees who work at both companies.

Scenario:

Company A has a table called EmployeesA, where employee records are stored. Company B has a table called EmployeesB, with its own employee records. We need to find out which employees work for both companies.

SQL Query using INTERSECT:

SELECT EmployeeID, Name FROM EmployeesA INTERSECT SELECT EmployeeID, Name FROM EmployeesB;

In this scenario, EmployeesA holds the employees of Company A, and EmployeesB holds the employees of Company B. The INTERSECT operator will return the employees who work at both companies.

Real-Life Interpretation:

Imagine these two companies operate in the same business park. Due to a recent collaboration, they've been sharing specific facilities. The goal is to identify the employees common to both companies for coordinated access to the shared facilities.

By using the INTERSECT operator, we can generate a list of employees who work at both companies. This helps streamline facility access and facilitates collaboration between the two companies for joint projects or initiatives.