PL/SQL INTERSECT Operator
The PL/SQL INTERSECT operator is a powerful SQL set operation that allows us to return only the rows that are common to two or more SELECT queries.
Unlike UNION or UNION ALL, which combine the results of different queries, INTERSECT focuses on finding the overlap between them. In this article, We will learn about PL/SQL INTERSECT Operator by understanding various examples in detail.
What is the PL/SQL INTERSECT Operator?
The PL/SQL INTERSECT operator is used to combine the results of two or more SELECT queries, returning only the rows that are common to all queries. It automatically removes duplicates from the final result set and ensures that each row appears only once.
Syntax
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
Explanation:
- SELECT column1, column2, ...: Specifies the columns to retrieve from the first query.
- FROM table1: Indicates the table or subquery from which the data is being retrieved.
- INTERSECT: Combines the results of the first SELECT statement with the results of the second SELECT statement, returning only the rows that are present in both.
- SELECT column1, column2, ...: Specifies the columns to retrieve from the second query.
- FROM table2: Indicates the table or subquery from which the data is being retrieved.
Example of PL/SQL INTERSECT Operator
Let's create two tables named employees and projects with sample data.
projects Table
project_id | employee_id | project_name |
---|---|---|
1 | 2 | Project X |
2 | 3 | Project Y |
3 | 4 | Project Z |
4 | 1 | Project A |
employees Table
employee_id | employee_name | department |
---|---|---|
1 | Alice | HR |
2 | Bob | IT |
3 | Charlie | Finance |
4 | David | IT |
Example 1: Finding Employees Who Are Working on Projects
This query returns the IDs and names of employees who are assigned to projects by intersecting the list of all employees with the list of employees who have an associated project.
SELECT e.employee_id, e.employee_name
FROM employees e
INTERSECT
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN projects p ON e.employee_id = p.employee_id;
Output:

Explanation
- The result set includes only employees who are involved in at least one project.
- Employees not assigned to any project are excluded.
Example 2: Identifying IT Department Employees Working on Projects
This query returns IT department employees who are working on projects by intersecting IT department employees with the list of employees involved in projects.
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.department = 'IT'
INTERSECT
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN projects p ON e.employee_id = p.employee_id;
Output

Explanation:
- The result set includes only IT employees who are working on projects.
- IT employees not involved in projects are excluded.
Example 3 : Finding Common Project Assignments
This query finds projects that are assigned to both Employee 2 and Employee 4 by intersecting their project lists.
Output
SELECT project_name
FROM projects
WHERE employee_id = 2
INTERSECT
SELECT project_name
FROM projects
WHERE employee_id = 4;
Output

Explanation
- Only projects common to both employees are included.
- Projects unique to each employee are excluded.
Example 4: Employees with the Same ID Across Tables
This query identifies employees who have the same ID in both the employees and projects tables, essentially listing employees who are working on projects.
SELECT employee_id
FROM employees
INTERSECT
SELECT employee_id
FROM projects;
Output

Explanation
- Only employees who have matching IDs in both tables are returned.
- Employees without a project or projects without an employee are excluded.
Conclusion
The PL/SQL INTERSECT operator is a powerful tool for retrieving common records between multiple datasets. By focusing on the overlap between queries, it allows you to isolate and analyze shared data efficiently. Understanding and using INTERSECT can enhance your ability to manage and query relational databases effectively.