Intersect operator - tricky interview questions

 The INTERSECT operator in SQL is used to return the common records from two or more SELECT statements. Here are some tricky interview questions related to the INTERSECT operator:

1. Basic Understanding:

• Question: How does the INTERSECT operator differ from the INNER JOIN?
• Answer: INTERSECT returns common rows from two SELECT statements, based on all columns, without needing explicit join conditions. INNER JOIN combines rows from two tables based on a related column but returns columns from both tables.

2. Order of Operations:

• Question: What happens if you use the INTERSECT operator between queries with different ORDER BY clauses?
• Answer: The INTERSECT operator doesn't preserve the order of the results from the original queries. If you need a specific order, you must apply an ORDER BY clause after the INTERSECT.

3. Null Handling:

• Question: How does the INTERSECT operator handle NULL values?
• Answer: The INTERSECT operator treats NULL as a comparable value. If NULL appears in the same position in both queries, it will be included in the results.

4. Performance Considerations:

• Question: How does the performance of INTERSECT compare to EXISTS or INNER JOIN for finding common records?
• Answer: INTERSECT can be slower than using EXISTS or INNER JOIN due to the fact that it eliminates duplicates and compares all columns. EXISTS might be faster when checking for existence without needing to return the exact rows.

5. Set Operations:

• Question: Can you use INTERSECT with more than two queries?
• Answer: Yes, you can chain multiple INTERSECT operations. The result will be the intersection of all the sets involved.

6. Combining with Other Set Operators:

• Question: What is the result of combining INTERSECT with UNION or EXCEPT?
• Answer: These set operators can be combined in a single query. For example, you can use INTERSECT to find common records and then use UNION to combine them with results from another query, or EXCEPT to exclude certain records.

7. Data Types:

• Question: What happens if the data types in the columns of the queries being intersected don’t match?
• Answer: For the INTERSECT operation to work, the number of columns and their data types must match. Otherwise, SQL will throw an error.

8. Complexity:

• Question: How would you use INTERSECT to find common records across three tables without directly using the INTERSECT keyword?
• Answer: You can achieve this by using nested INNER JOIN queries or EXISTS clauses. For example:

SELECT column_list
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table1.common_column = table2.common_column
)
AND EXISTS (
    SELECT 1
    FROM table3
    WHERE table1.common_column = table3.common_column
);

These questions test both the fundamental understanding of the INTERSECT operator and the candidate's ability to think critically about how it is used in more complex scenarios.