Oracle INTERSECT

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

  1. SELECT expression1, expression2, ... expression_n  
  2. FROM table1  
  3. WHERE conditions  
  4. INTERSECT  
  5. SELECT expression1, expression2, ... expression_n  
  6. FROM table2  
  7. 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

Oracle Intersect 1

Suppliers Data

Oracle Intersect 2

Order_details Table

Oracle Intersect 3

Order_details Data

Oracle Intersect 4

  1. SELECT supplier_id  
  2. FROM suppliers  
  3. INTERSECT  
  4. SELECT supplier_id  
  5. 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

Oracle INTERSECT Example: (with multiple expressions)

Supplier Table

Oracle Intersect 5

Supplier Data

Oracle Intersect 6

Customer Table

Oracle Intersect 7

Customer Data

Oracle Intersect 8

  1. SELECT supplier_id, last_name, first_name  
  2. FROM supplier  
  3. WHERE first_name <> 'dhirubhai'  
  4. INTERSECT  
  5. SELECT customer_id, last_name, first_name  
  6. FROM customer  
  7. WHERE customer_id < 5;  

Output

Oracle Intersect 9

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.