Oracle INTERSECT Operator
Published by : Obay Salah , December 1, 2024
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
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
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
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.
Comments
no comment yet!