Using INTERSECT Operator in Oracle

This article is written about how to use the Oracle INTERSECT operator with syntax and examples.

Description

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.

Intersect Query

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.

Syntax

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.

Note

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.

For example:

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.