Intersect - ( Oracle Operators )

In Oracle, INTERSECT is a set operator that is used to combine the results of two or more SELECT statements into a single result set. It returns all the rows that are common to all the input queries, i.e., the intersection of the result sets.

Syntax

The basic syntax for using the INTERSECT operator in Oracle is:

SELECT column1, column2, ... columnN
FROM table1
INTERSECT
SELECT column1, column2, ... columnN
FROM table2;

Here, the SELECT statements are the input queries whose results are to be intersected. The columns in both the SELECT statements must match in number and data type.

Example

Consider the following two tables employees and departments:

employees			departments
emp_id		dept_id	dept_name
101		1	sales
102		2	marketing
103		3	IT
104		4	HR

To find the employees who belong to both the sales and IT departments, we can use the INTERSECT operator as follows:

SELECT emp_id
FROM employees
WHERE dept_id = 1

INTERSECT

SELECT emp_id
FROM employees
WHERE dept_id = 3;

Output

EMP_ID
-----
101

Explanation

In the above example, we select the emp_id of all the employees in the sales department and then intersect it with the emp_id of all the employees in the IT department. The result of the INTERSECT operator is a single row containing the emp_id of the employee who belongs to both the departments.

Use

The INTERSECT operator is useful when we want to find the common elements in two or more result sets. It can be used to find the intersection of data from different tables or even subqueries.

Important Points

• The INTERSECT operator is used to combine the results of two or more SELECT statements.
• It returns only the rows that are common to all the input queries.
• The columns in the input queries must match in number and data type.
• The result set of the INTERSECT operator cannot have duplicate rows.

Summary

In summary, the INTERSECT operator in Oracle is used to combine the results of two or more SELECT statements and return only the rows that are common to all the input queries. It is used to find the intersection of data from different tables or subqueries and is useful in situations where we need to find the common elements in multiple result sets.