INTERSECT

The INTERSECT operator in Oracle SQL is used to return the common rows from two SELECT statements. It returns only the rows that are present in both result sets. This operator is commonly used to find common data between two tables or queries. Similar to set theory in mathematics, INTERSECT returns the intersection of two sets.

1. Basic Syntax of INTERSECT

SELECT column1, column2, ...

FROM table1

WHERE condition1

INTERSECT

SELECT column1, column2, ...

FROM table2

WHERE condition2;

• Both SELECT statements must return the same number of columns.
• The data types of the corresponding columns must be compatible.
• The result set contains only those rows that are common to both SELECT statements.

2. Key Characteristics of INTERSECT

a. Eliminates Duplicates by Default

• Like UNION, INTERSECT automatically eliminates duplicates. If a row appears multiple times in both SELECT statements, only one instance of that row will be included in the result set.

b. Same Number of Columns and Compatible Data Types

• The SELECT statements involved in the INTERSECT operation must have the same number of columns.
• The corresponding columns must have compatible data types. For example, a VARCHAR column can intersect with another VARCHAR column, but not with a NUMBER column.

c. Order of Queries

• The first query (before INTERSECT) is considered the base query, and the results will be compared against the second query.
• The result set will only include rows that are in both result sets, meaning the rows in the first result set that also appear in the second.

3. How INTERSECT Works

a. Intersection of Two Result Sets

• The result of INTERSECT is the set of rows that exist in both SELECT statements.
• For two rows to be considered the same (i.e., to appear in the intersection), they must have exactly the same values in each column of the result set.

Example:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2;

This will return all values from column1 that are present in both table1 and table2.

b. Removes Duplicates

• INTERSECT eliminates duplicate rows by default. Even if a row appears multiple times in both SELECT statements, it will only appear once in the result set.

Example:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2;

If table1 has two occurrences of 'A' and table2 has one occurrence of 'A', the result will return 'A' only once.

4. Use Cases for INTERSECT

a. Finding Common Records

• INTERSECT is commonly used to find common records between two tables or queries. For example, if you want to identify employees that are present in both the employees table and the contractors table:

SELECT employee_id FROM employees

INTERSECT

SELECT employee_id FROM contractors;

This query will return the employee_ids that exist in both the employees and contractors tables.

b. Comparing Two Data Sets

• You can use INTERSECT to compare two datasets and retrieve only the rows that are shared between them.

For example, comparing two sets of product IDs:

SELECT product_id FROM supplier1_products

INTERSECT

SELECT product_id FROM supplier2_products;

This will return all product IDs that are found in both suppliers' product lists.

c. Identifying Overlapping Data

• INTERSECT is helpful when you need to identify overlapping or matching data between two result sets, especially when both sets are expected to contain some shared data points.

Example:

SELECT order_id FROM orders

INTERSECT

SELECT order_id FROM payments;

This query will return all order IDs that have entries in both the orders table and the payments table, meaning the orders that have been paid.

5. Performance Considerations

• Removing Duplicates: Since INTERSECT removes duplicates, it may require more processing time than UNION ALL (which doesn’t remove duplicates). For large datasets, the database engine must compare and eliminate duplicates from both result sets.
• Indexes: Performance can be improved by having indexes on the columns involved in the INTERSECT operation, especially if the tables contain large amounts of data.
• Optimizing Queries: Ensure that the SELECT statements are optimized, as INTERSECT operates over the entire result set of both queries.

6. Examples of Using INTERSECT

Example 1: Common Employees in Two Tables

You have two tables: employees and contractors. To find employees who are also listed as contractors:

SELECT employee_id FROM employees

INTERSECT

SELECT employee_id FROM contractors;

This query will return all employee_ids that exist in both the employees and contractors tables.

Example 2: Finding Common Products Between Two Suppliers

You want to find the common products between two suppliers, supplier1_products and supplier2_products.

SELECT product_id FROM supplier1_products

INTERSECT

SELECT product_id FROM supplier2_products;

This will return all product IDs that exist in both suppliers' product lists.

Example 3: Finding Common Orders in orders and payments Tables

To find orders that have corresponding payment records, you can use INTERSECT:

SELECT order_id FROM orders

INTERSECT

SELECT order_id FROM payments;

This will return the order_ids that are present in both the orders and payments tables.

7. Handling INTERSECT with More Complex Queries

You can use INTERSECT in more complex queries involving joins or subqueries.

Example: Common Records After Join

If you have two tables, employees and contractors, and want to find the common departments where both employees and contractors work, you could do:

SELECT department FROM employees

INTERSECT

SELECT department FROM contractors;

This will return the list of departments that are common to both employees and contractors.

Example with Subqueries

You can also use subqueries with INTERSECT. For example, finding orders that were both completed and paid:

SELECT order_id 

FROM (SELECT order_id FROM orders WHERE order_status = 'completed')

INTERSECT

SELECT order_id 

FROM (SELECT order_id FROM payments WHERE payment_status = 'successful');

8. INTERSECT vs. MINUS

• INTERSECT: Returns the rows that are common to both SELECT queries.
• MINUS: Returns the rows that are in the first SELECT query but not in the second.

For example:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2;

This query returns common values between table1 and table2.

SELECT column1 FROM table1

MINUS

SELECT column1 FROM table2;

This query returns values from table1 that are not in table2.

9. Can INTERSECT be used with ORDER BY?

Yes, you can use ORDER BY with INTERSECT, but the ORDER BY clause must be placed at the end of the entire query:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2

ORDER BY column1;

This will return the intersection of column1 from both tables, sorted in ascending order.

10. Can INTERSECT be used with GROUP BY?

Yes, you can use INTERSECT with aggregate functions and GROUP BY. For example, if you want to find the departments where both employees and contractors have the same average salary:

SELECT department, AVG(salary) 

FROM employees

GROUP BY department

INTERSECT

SELECT department, AVG(salary) 

FROM contractors

GROUP BY department;

This will return departments where the average salary for employees matches the average salary for contractors.