Oracle INTERSECT Operator

If you have the need to compare results between two or more queries in the database, there is an ORACLE
this feature is INTERSECT. It returns all rows selected by all queries or datasets that are present in the query. If a record is in one query and not the other, it is omitted by INTERSECT.

The syntax for the INTERSECT operator is:

SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions]

INTERSECT

SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions];

Example – With Unique Expression
The INTERSECT usage example below returns a field with the same data type:

SELECT supplier_id
FROM suppliers

INTERSECT

SELECT supplier_id
FROM orders;

Example – with multiple expressions
In the example below, see how to use the INTERSECT operator to return more than one column.

SELECT contact_id, last_name, first_name
FROM contacts
WHERE first_name <> ‘John’

INTERSECT

SELECT customer_id, last_name, first_name
From customers
WHERE customer_id> = 89;

Well, I’ll stop here. Inquiries, please contact us.

Strong hug.

Eduardo Santana
bufallos@bufallos.com.br