Union Operator - tricky interview questions

 The UNION operator in SQL is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements. Here are some tricky interview questions that test your understanding of the UNION operator:

1. Basic Understanding:

• Question: What is the difference between UNION and UNION ALL?
• Answer: UNION removes duplicate rows from the combined result set, whereas UNION ALL includes all rows, even duplicates.

2. Column Order and Type:

• Question: What happens if the columns in the SELECT statements used in a UNION query are not in the same order?
• Answer: The columns must be in the same order for all SELECT statements in a UNION. If they are not, SQL will throw a syntax error or produce incorrect results because the database engine matches columns by position, not by name.

3. Different Data Types:

• Question: Can you use UNION to combine result sets where the corresponding columns have different data types?
• Answer: The corresponding columns in the SELECT statements must have compatible data types. For example, you can combine an INT with a FLOAT, but not an INT with a VARCHAR unless an explicit conversion is done.

4. Order of Execution:

• Question: How does SQL handle the ORDER BY clause in a UNION query?
• Answer: The ORDER BY clause applies to the entire result set, not to individual SELECT statements. If you want to order the combined results, you must place the ORDER BY at the end of the last SELECT statement in the UNION query.

5. Performance Considerations:

• Question: Given two large tables, what are some performance considerations when using UNION vs. UNION ALL?
• Answer: UNION has a performance overhead because it eliminates duplicates, which requires sorting or a hashing operation. UNION ALL is faster because it does not perform duplicate elimination.

6. Using UNION with NULLs:

• Question: What happens when NULL values are present in the columns being combined with a UNION?
• Answer: UNION treats NULL values as equivalent when removing duplicates, so if two rows differ only by having NULL values in some columns, one of those rows will be removed from the final result set.

7. Complex Query with WHERE Clauses:

• Question: Consider two tables, Employees and Contractors, both having columns ID, Name, and Department. Write a UNION query that retrieves all Employees from the IT department and all Contractors from the HR department.
• Answer:

SELECT ID, Name, Department FROM Employees WHERE Department = 'IT'
UNION
SELECT ID, Name, Department FROM Contractors WHERE Department = 'HR';

8. Subqueries with UNION:

• Question: How would you use a UNION in conjunction with a subquery to retrieve the top 5 highest-paid employees and the top 5 highest-paid contractors from two different tables?
• Answer:

(SELECT TOP 5 ID, Name, Salary FROM Employees ORDER BY Salary DESC)
UNION
(SELECT TOP 5 ID, Name, Salary FROM Contractors ORDER BY Salary DESC);

9. Handling Inconsistent Data Across Tables:

• Question: Suppose you have two tables with inconsistent data (e.g., one table uses uppercase names, and the other uses lowercase). How can you write a UNION query that treats names case-insensitively?
• Answer:

SELECT LOWER(Name) AS Name FROM Table1
UNION
SELECT LOWER(Name) AS Name FROM Table2;

10. Detecting and Handling Inconsistencies:

• Question: How would you modify a UNION query to detect and flag inconsistencies in corresponding columns from the two tables being combined?
• Answer:

SELECT Name, 'Source: Table1' AS Source FROM Table1
UNION
SELECT Name, 'Source: Table2' AS Source FROM Table2
WHERE Name NOT IN (SELECT Name FROM Table1);

This query flags names present in Table2 but not in Table1, indicating a possible inconsistency.