INTERSECT FAQS

 1. What is the purpose of the INTERSECT operator in Oracle SQL?

The INTERSECT operator returns the common rows from two SELECT statements. It finds the intersection of two result sets, returning only those rows that appear in both queries.

2. How is INTERSECT different from UNION?

• INTERSECT: Returns only the rows that are common to both result sets.
• UNION: Combines two result sets and returns all unique rows from both queries, removing duplicates.

3. Does INTERSECT remove duplicates?

Yes, INTERSECT removes duplicates by default. If a row appears multiple times in both result sets, it will appear only once in the final result.

4. Can the SELECT statements involved in an INTERSECT operation have different numbers of columns?

No, the SELECT statements involved in an INTERSECT operation must return the same number of columns. The columns must also have compatible data types.

5. Can I use INTERSECT with columns of different data types?

The columns involved in the INTERSECT must have compatible data types. For example, you can intersect two VARCHAR columns, but not a VARCHAR column with a NUMBER column.

6. Can INTERSECT be used with ORDER BY?

Yes, you can use an ORDER BY clause with INTERSECT to sort the final result set. The ORDER BY clause must be placed at the end of the query:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2

ORDER BY column1;

7. How does INTERSECT compare to MINUS?

• INTERSECT returns the rows that are common to both result sets.
• MINUS returns the rows from the first result set that do not appear in the second result set.

For example:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2;

This will return the rows common to both tables.

SELECT column1 FROM table1

MINUS

SELECT column1 FROM table2;

This will return the rows that exist in table1 but not in table2.

8. Can INTERSECT be used with aggregate functions like COUNT, AVG, etc.?

Yes, you can use INTERSECT with aggregate functions. Just like any SELECT statement, you can use GROUP BY and aggregate functions, and then intersect the result sets.

For example:

SELECT department, AVG(salary)

FROM employees

GROUP BY department

INTERSECT

SELECT department, AVG(salary)

FROM contractors

GROUP BY department;

This returns departments where the average salary matches between employees and contractors.

9. Can I use INTERSECT with subqueries?

Yes, you can use INTERSECT with subqueries. Here’s an example:

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');

This query finds orders that are both completed and successfully paid.

10. What happens if the two SELECT statements in INTERSECT have different results?

INTERSECT will return only the rows that are present in both result sets. Any rows that appear in one query but not the other will be excluded from the final result.

11. Can I use INTERSECT with NULL values?

Yes, INTERSECT treats NULL values as equal if they appear in both result sets. So if NULL appears in both SELECT statements, it will be included in the result set.

Example:

SELECT column1 FROM table1

INTERSECT

SELECT column1 FROM table2;

If both table1 and table2 have NULL in column1, that NULL will appear in the result.

12. What happens if there is no common data between the two SELECT statements?

If there are no common rows between the two SELECT statements, the result of the INTERSECT query will be empty (i.e., no rows).

13. Can INTERSECT be used with complex queries or joins?

Yes, you can use INTERSECT with complex queries or joins. For example, you can join tables and then use INTERSECT to find the common records between the joined result sets.

Example:

SELECT department_id FROM employees

JOIN departments ON employees.department_id = departments.department_id

INTERSECT

SELECT department_id FROM contractors

JOIN departments ON contractors.department_id = departments.department_id;

This finds departments common to both employees and contractors.

14. Is INTERSECT supported by all SQL databases?

No, INTERSECT is supported by many SQL databases, including Oracle, PostgreSQL, and SQL Server, but not all databases support the operator. For example, MySQL does not support INTERSECT directly. In such cases, you would need to rewrite the query using JOIN or IN to achieve the same result.

15. How do I handle performance issues with INTERSECT?

• Large datasets: INTERSECT can be slow with large datasets because it has to compare all rows between the two result sets and remove duplicates.
• Indexes: Performance can be improved by ensuring that the columns involved in the INTERSECT operation are indexed.
• Query Optimization: Ensure that both SELECT statements are optimized, especially if they involve joins or subqueries. Using EXPLAIN PLAN to analyze query performance can also help identify bottlenecks.