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.

Consider the following query with the INTERSECT operator.

SELECT * FROM Employee
INTERSECT
    SELECT * from Employee_backup

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

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.

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.

SELECT * FROM Employee
WHERE Salary > 15000
INTERSECT
    SELECT * from Employee_backup