Oracle INTERSECT Operator

In Oracle, INTERSECT Operator is used to return the results of 2 or more SELECT statement. It picks the common or intersecting records from compound SELECT queries.

Syntax

snippet

SELECT expression1, expression2, ... expression_n
FROM table1
WHERE conditions
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM table2
WHERE conditions;

Parameters

1) expression1, expression2, ... expression_n: It specifies the columns that you want to retrieve.

2) table1, table2: It specifies the tables that you want to retrieve records from.

3) conditions: it specifies the conditions that must be fulfilled for the records to be selected.

Oracle INTERSECT Example: (with single expression)

Suppliers Table

Suppliers Data

Order_details Table

Order_details Data

snippet

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM order_details;

In the above example, the supplier_id appears in both the suppliers and order_details table. Now the common entries will be returned in the result set.

Output

Oracle INTERSECT Example: (with multiple expressions)

Supplier Table

Supplier Data

Customer Table

Customer Data

snippet

SELECT supplier_id, last_name, first_name
FROM supplier
WHERE first_name <> 'dhirubhai'
INTERSECT
SELECT customer_id, last_name, first_name
FROM customer
WHERE customer_id < 5;

Output

The above example returns the records from the supplier table where the supplier_id, last_name and first_name values match the customer_id, last_name, and first_name value of customer table.