PL/SQL INTERSECT Operator
Last Updated : 10 Sep, 2024
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:
Finding Employees Who Are Working on ProjectsExplanation
- 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
Identifying IT Department Employees Working on ProjectsExplanation:
- 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
Finding Common Project AssignmentsExplanation
- 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
Employees with the Same ID Across TablesExplanation
- 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.
Similar Reads
SQLite Intersect Operator
SQLite is a server-less database engine written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is escaping complex database engines like MYSQL. It has become one of the most popular database engines as we use it in Television, Mobi
5 min read
SQL Server INTERSECT Operator
In SQL Server, the INTERSECT operator is a kind of set operator that is used to combine the results of two SELECT statements and return rows which is common between them. In this article, We will explore the syntax, key concepts, and practical examples of using the INTERSECT operator. Whether you ar
5 min read
PL/SQL IN Operator
The PL/SQL IN operator is a powerful tool used in SQL queries to check if a value matches any value in a list or a subquery result. It simplifies querying multiple values and can make your SQL code cleaner and more readable. The IN operator is typically used in the WHERE clause to filter results bas
6 min read
PL/SQL NOT Operator
PL/SQL, an extension of SQL in Oracle, offers various operators that allow us to perform logical operations on data. One such operator is the NOT operator, which is used to negate a condition, meaning it will return true if the condition is false and vice versa. The NOT operator is commonly used in
6 min read
PL/SQL EXISTS Operator
The EXISTS operator in PL/SQL is a powerful tool used to check the existence of records in a subquery. Unlike traditional comparison operators that evaluate data values, EXISTS focuses on whether a set of conditions returns any rows. It is commonly used to determine the presence or absence of record
6 min read
PL/SQL AND Operator
The PL/SQL AND operator is used to combine multiple conditions in a WHERE clause of an SQL query. It allows you to refine your query by ensuring that all specified conditions are met. AND queries which help in filtering data more precisely and can be crucial for retrieving accurate results from a da
7 min read
SQL IN Operator
The SQL IN operator filters data based on a list of specific values. In general, we can only use one condition in the Where clause, but the IN operator allows us to specify multiple values. In this article, we will learn about the IN operator in SQL by understanding its syntax and examples. IN Opera
4 min read
PL/SQL IS NULL Operator
The IS NULL operator is a fundamental tool in PL/SQL used to determine the presence of NULL values in database columns. Understanding how to effectively use the IS NULL operator is crucial for database management, as it allows developers and analysts to identify and handle records with missing or un
4 min read
PL/SQL Operators
The PL/SQL language offers various operators for data manipulation and logical processing. There are several types of these operators which include arithmetic operators, relational operators, comparison operators, and logical operators. In this guide, we will learn about the various PL/SQL operators
4 min read
SQL NOT Operator
The SQL NOT Operator is a logical operator used to negate or reverse the result of a condition in SQL queries. It is commonly used with the WHERE clause to filter records that do not meet a specified condition, helping you exclude certain values from your results. In this article, we will learn ever
3 min read