| ORACLE | ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
|
Oracle - Intersect
The Oracle INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
SELECT expression1, expression2, ... expression_n
FROM table1
WHERE conditions
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM table2
WHERE conditions;
| SUPPLIER_ID | SUPPLIER_NAME | SUPPLIER_ADDRESS |
|---|---|---|
| 1 | Bata shoes | Agra |
| 2 | Kingfisher | Delhi |
| 3 | VOJO | Lucknow |
| 4 | Apple | - |
| 5 | Flipkart | - |
| SUPPLIER_ID | SUPPLY_DATE | SUPPLY_ADDRESS |
|---|---|---|
| 1 | 5-12-19 | Ahmedabad |
| 2 | 6-12-19 | Bangalore |
| 3 | 7-12-19 | Mumbai |
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.
| SUPPLIER_ID |
|---|
| 1 |
| 2 |
| 3 |
| SUPPLIER_ID | FIRST_NAME | LAST_NAME |
|---|---|---|
| 1 | dhirubhai | ambani |
| 2 | kanchabhai | motwani |
| 3 | lallu | lal |
| CUSTOMER_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
|---|---|---|---|
| 1 | Aryan | tomar | - |
| 2 | Ajeet | maurya | - |
| 3 | richa | goyal | - |
| 4 | dhirubhai | jotwani | - |
| 32 | alex | pandian | 1 |
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;
| First_name | Last_name |
|---|---|
| maurya | ajeet |
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.