INTERSECT Operator - ( Oracle Misc )

The INTERSECT operator in Oracle is used to combine the results of two SELECT statements and produce a result set that contains only the common rows between the two result sets. It is similar to the UNION operator, but instead of combining the results of both SELECT statements, it returns only the common rows.

Syntax

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

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

Here, column1, column2, etc. are the column names to be returned in the result set, and table1 and table2 are the tables to be queried.

Example

Consider the following tables:

Table1

id	name	age
1	Alice	25
2	Bob	30
3	Charlie	35

Table2

id	name	age
2	Bob	30
3	Charlie	35
4	David	40

To find the common rows between these two tables based on id, we can use the INTERSECT operator as follows:

SELECT id, name, age
FROM table1
INTERSECT
SELECT id, name, age
FROM table2;

The result will be:

id	name	age
2	Bob	30
3	Charlie	35

Explanation

In the above example, we have used the INTERSECT operator to find the common rows between two tables based on the id column. The first SELECT statement selects all rows from table1, and the second SELECT statement selects all rows from table2. The INTERSECT operator then compares these two result sets and returns only the rows that are common to both, based on the id column.

Use

The INTERSECT operator can be used to find the common rows between two tables or result sets. It is useful when you need to find the intersection of two sets of data, and only want to return the rows that are common to both.

Important Points

• The INTERSECT operator in Oracle is used to combine the results of two SELECT statements and produce a result set that contains only the common rows between the two result sets.
• The two SELECT statements must return the same number of columns and data types.
• The INTERSECT operator excludes duplicate rows from the final result set.

Summary

In summary, the INTERSECT operator in Oracle is used to find the common rows between two tables or result sets. It is useful when you need to find the intersection of two sets of data, and only want to return the rows that are common to both. The two SELECT statements must return the same number of columns and data types, and the operator excludes duplicate rows from the final result.