This article is written about how to use the Oracle INTERSECT operator with syntax and examples.
The Oracle INTERSECT operator is used to return the effects of two or extra SELECT statements. However, it only returns the rows chosen through all queries or information sets. If a record exists in one query and not in the other, it will be neglected from the INTERSECT results.
Explanation: The INTERSECT query will return the records in the blue shaded area. These are the data that exist in each Dataset1 and Dataset2.
Each SELECT announcement inside the INTERSECT must have the same variety of fields in the result sets with similar facts types.
The syntax for the INTERSECT operator in Oracle/PLSQL is:
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] INTERSECT SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
Parameters or Arguments
expression1, expression2, … expression_n The columns or calculations that you want to retrieve. tables The tables that you desire to retrieve records from. There must be at least one desk listed in the FROM clause. WHERE conditions Optional. The conditions that should be met for the files to be selected.
There should be identical wide variety of expressions in each SELECT statements and have comparable information types.
Example – With Single Expression
The following is an Oracle INTERSECT example that returns one discipline with the equal records type:
SELECT supplier_id FROM suppliers INTERSECT SELECT supplier_id FROM orders;
In this INTERSECT example, if a supplier_id seemed in both the suppliers and orders table, it would show up in your result set.
Now, let’s complicate our instance further by adding WHERE stipulations to the INTERSECT query.
SELECT supplier_id FROM suppliers WHERE supplier_id <= 99 INTERSECT SELECT supplier_id FROM orders WHERE quantity > 25;
In this example, the WHERE clauses have been delivered to each of the datasets. The first dataset has been filtered so that only data from the suppliers desk the place the supplier_id is less than or equal to ninety nine are returned. The second dataset has been filtered so that only information from the orders desk are again the place the volume is larger than 25.
Example – With Multiple Expressions
Next, let’s look at an instance of how to use the INTERSECT operator in Oracle to return extra 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;
In this INTERSECT example, the query will return the files from the contacts table the place the contact_id, last_name, and first_name values healthy the customer_id, last_name, and first_name fee from the clients table.
There are WHERE stipulations on every records set to in addition filter the effects so that only documents from the contacts are back where the first_name is not John. The archives from the clients table are returned the place the customer_id is larger than or equal to 89.
Example – Using ORDER BY
The following is an INTERSECT instance that uses an ORDER BY clause:
SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id > 500 INTERSECT SELECT company_id, company_name FROM companies WHERE company_name in ( 'Apple', 'Microsoft', 'Oracle' ) ORDER BY 2;
Since the column names are exceptional between the two SELECT statements, it is extra positive to reference the columns in the ORDER BY clause with the aid of their role in the end result set. In this example, we have sorted the effects by means of supplier_name / company_name in ascending order, as denoted with the aid of the ORDER BY 2
The supplier_name / company_name fields are in role #2 in the result set.