Oracle INTERSECT Operator with Examples

The Oracle INTERSECT operator is used for finding the intersection of results of two queries.

Syntax:

The syntax of Oracle INTERSECT set operator is as follows:

SELECT
    column1, column2, column_n ...
FROM
    Table1
INTERSECT 
SELECT
    column1, column2, column_n ...
FROM
    Table2;

Here, column1, column2 .. – List of columns in each table.

Table1 and Table2 – The name of tables whose result set we want to combine using Set operator.

Let’s revisit the concept of how set operators work in mathematics, before we see examples in Oracle:

Note:

We need to keep 2 things in mind while using Set Operator in Oracle:

1. The number and order of columns must be same in both the subqueries.
2. The data type group of corresponding columns should match.

Let’s see some examples of INTERSECT operator.

Oracle INTERSECT Example

Let’s create two tables named dept_a and dept_b for these examples:

CREATE TABLE dept_a (
    intern_id INT PRIMARY KEY,
    name VARCHAR2 (50) NOT NULL
);

CREATE TABLE dept_b (
    intern_id INT PRIMARY KEY,
    name VARCHAR2 (50) NOT NULL
);

-- insert data in department_a table

INSERT INTO dept_a (intern_id, name)
VALUES (1, 'Eva');

INSERT INTO dept_a (intern_id, name)
VALUES (2, 'Sia');

INSERT INTO dept_a (intern_id, name)
VALUES (3, 'Tim');

INSERT INTO dept_a (intern_id, name)
VALUES (4, 'Ray');

-- insert data in dept_b table

INSERT INTO dept_b (intern_id, name)
VALUES (3, 'Tim');

INSERT INTO dept_b (intern_id, name)
VALUES (4, 'Ray');

INSERT INTO dept_b (intern_id, name)
VALUES (5, 'Ashley');

INSERT INTO dept_b (intern_id, name)
VALUES (6, 'Alexa');

Result:

We get two tables as following:

Let’s use INTERSECT operator in query to find the common interns in both departments:

SELECT
    intern_id,
    name
FROM
    department_a
INTERSECT
SELECT
    intern_id,
    name
FROM
    department_b;

Result:

The query returns a list of 2 interns which are common in both department tables.

In this tutorial, we learned about the usage of INTERSECT Set Operator in Oracle to find common values in result set of two queries.