INTERSECT Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

INTERSECT Operator in Oracle with Examples

In this article, I am going to discuss INTERSECT Operator in Oracle with Examples. Please read our previous article, where we discussed UNION ALL Operator in Oracle with Examples. At the end of this article, you will understand INTERSECT Operator in detail with Examples.

INTERSECT Operator in Oracle

The INTERSECT operator in Oracle is used to combine two result sets and returns the data which are common in both the result set. That means the INTERSECT Operator returns only those rows that are common in both the result sets. Following is the pictorial representation of INTERSECT Operator.

Following is the syntax of INTERSECT operator.

Examples to understand INTERSECT Operator in Oracle:

We are going to use the following EmployeeUK and EmployeeUSA tables to understand the INTERSECT Operator in Oracle with examples.

Please use the below SQL Script to create the EmployeeUK and EmployeeUSA tables with the required data.

SET linesize 300;
DROP Table EmployeeUK;
DROP Table EmployeeUSA;

CREATE TABLE EmployeeUK
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUK VALUES(1, 'Pranaya', 'Rout', 'Male','IT');
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUK VALUES(3, 'Preety', 'Tiwary', 'Female','HR');
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUK VALUES(5, 'Anurag', 'Mohanty', 'Male','IT');
INSERT INTO EmployeeUK VALUES(6, 'Rajesh', 'Pradhan', 'Male','HR');
INSERT INTO EmployeeUK VALUES(7, 'Hina', 'Sharma', 'Female','IT');

CREATE TABLE EmployeeUSA
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUSA VALUES(8, 'James', 'Pattrick', 'Male','IT');
INSERT INTO EmployeeUSA VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUSA VALUES(9, 'Sara', 'Taylor', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(10, 'Sushanta', 'Jena', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(11, 'Mahesh', 'Sindhey', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(7, 'Hina', 'Sharma', 'Female','IT');

INTERSECT Operator Example in Oracle:

The following query combines the result sets of two select statements into a single result set using the INTERSECT operator in Oracle.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
INTERSECT
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

The above statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries. Once you execute the above INTERSECT query, you will get the following result set. Please observe, here we got only 3 rows in the result set which are common in both the result set.

Replacing INTERSECT with Inner JOIN in Oracle:

We can achieve the previous example output using Oracle Inner Join. The following INNER join query would produce the exact same result. 

SELECT USA.FirstName, USA.LastName, USA.Gender, USA.Department
FROM EmployeeUK UK INNER JOIN EmployeeUSA USA
ON UK.EmployeeID = USA.EmployeeID;

When you execute the above query, you will get the following output.

What is the difference between INTERSECT and INNER JOIN in Oracle?

The INTERSECT Operator filters duplicate rows and return only the DISTINCT rows that are common between the LEFT and Right Query, whereas INNER JOIN does not filter the duplicates. To understand this difference, insert the following row into the EmployeeUK table.

INSERT INTO EmployeeUK VALUES(2, ‘Priyanka’, ‘Dewangan’, ‘Female’,’IT’);

Now execute the following INTERSECT query. Notice that we get only the DISTINCT rows in the output.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
INTERSECT
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

When you execute the above INTERSECT query, you will get the following output.

Now execute the following INNER JOIN query. Notice that the duplicate rows are not filtered.

SELECT USA.FirstName, USA.LastName, USA.Gender, USA.Department
FROM EmployeeUK UK INNER JOIN EmployeeUSA USA
ON UK.EmployeeID = USA.EmployeeID;

When you execute the above INNER JOIN query, you will get the following output.

We can make the INNER JOIN behave like INTERSECT operator by using the DISTINCT operator in Oracle as shown below.

SELECT DISTINCT USA.FirstName, USA.LastName, USA.Gender, USA.Department
FROM EmployeeUK UK INNER JOIN EmployeeUSA USA
ON UK.EmployeeID = USA.EmployeeID;

Now, when you execute the above INNER JOIN query using the DISTINCT Operator, you will get the following output.

In the next article, I am going to discuss MINUS Operator in Oracle with Examples. Here, in this article, I try to explain INTERSECT Operator in Oracle with Examples and I hope you enjoy this INTERSECT Operator in Oracle with Examples article. If you have any queries regarding the Oracle INTERSECT Operator, then please let us know by putting your query in the comment section.