SQL - Intersect Operator

The INTERSECT operator returns common result set of two or more SELECT queries. It returns all common records that are exist in the result set of all INTERSECT sub-queries.

Syntax:

SELECT column_name1, column_name2,...
FROM tables
[WHERE Condition]
INTERSECT
    SELECT column_name1, column_name2, ...
    FROM tables
    [WHERE Condition];

For the demo purpose, we will use the following tables in all examples.

Employee Table
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
Employee_backup
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
6 'Abdul' 'K' '[email protected]' 25000 2020-07-14
7 'Swati' 'Karia' '[email protected]' 22000 2020-09-18

Consider the following query with the INTERSECT operator.

SQL Script: INTERSECT Operator
SELECT * FROM Employee
INTERSECT
    SELECT * from Employee_backup

Above query will return the common records in both the tables, as shown below.

EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22

Notice that the above result does not include 'Abdul' because the LastName values are different.

Note that both the queries must have equal number of expressions in their SELECT clause. The following query will raise an error.

SQL Script: Error with INTERSECT
SELECT * FROM Employee
INTERSECT
    SELECT EmpId, FirstName from Employee_backup

You can use the WHERE clause with any or all intersect queries, as shown below.

SQL Script: INTERSECT Query
SELECT * FROM Employee
WHERE Salary > 15000
INTERSECT
    SELECT * from Employee_backup
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22