10 SQL Union Interview Questions and Answers

SQL Union is a powerful tool in database management, allowing users to combine the results of two or more SELECT queries into a single result set. This operation is essential for efficiently handling and analyzing large datasets, making it a critical skill for anyone working with relational databases. Mastery of SQL Union can significantly enhance your ability to perform complex data manipulations and generate comprehensive reports.

This article offers a curated selection of SQL Union interview questions designed to test and improve your understanding of this key concept. By working through these questions, you will gain deeper insights into the practical applications of SQL Union, preparing you to tackle real-world database challenges with confidence.

SQL Union Interview Questions and Answers

1. Write a basic SQL query using UNION to combine results from two tables with identical structures.

The SQL UNION operator combines results from multiple SELECT statements into a single result set. Each SELECT must have the same number of columns with similar data types, in the same order.

Example:

SELECT column1, column2, column3
FROM table1
UNION
SELECT column1, column2, column3
FROM table2;

This example combines results from table1 and table2, removing duplicate rows by default. To include duplicates, use UNION ALL:

SELECT column1, column2, column3
FROM table1
UNION ALL
SELECT column1, column2, column3
FROM table2;

2. Explain the difference between UNION and UNION ALL. When would you use one over the other?

UNION and UNION ALL combine results from multiple SELECT statements. The key difference is that UNION removes duplicates, while UNION ALL includes all rows, including duplicates. Use UNION for unique results and UNION ALL for faster performance when duplicates are acceptable.

Example:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

This removes duplicates.

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

This includes duplicates.

3. Write a query using UNION to combine results from three tables, ensuring no duplicate rows are included.

To combine results from three tables without duplicates:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
UNION
SELECT column1, column2 FROM table3;

This ensures no duplicate rows in the final result.

4. Explain how the ORDER BY clause works with UNION. Provide an example query.

The ORDER BY clause sorts the final result set of a UNION operation. It cannot be used within individual SELECT statements. Place it at the end of the UNION to sort the combined results.

Example:

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2
ORDER BY column1;

This sorts the combined result by column1.

5. Write a query using UNION to combine results from two tables, but include an additional calculated column in the final result.

To include a calculated column in a UNION result:

SELECT 
    column1, 
    column2, 
    (column1 + column2) AS calculated_column
FROM 
    table1
UNION
SELECT 
    column1, 
    column2, 
    (column1 + column2) AS calculated_column
FROM 
    table2;

This adds a calculated column to the combined results.

6. Write a complex query using UNION to combine results from multiple tables, including nested subqueries and aggregate functions.

For a complex query using UNION with nested subqueries and aggregate functions:

SELECT employee_id, employee_name, total_sales
FROM (
    SELECT e.employee_id, e.employee_name, SUM(s.sales_amount) AS total_sales
    FROM employees e
    JOIN sales s ON e.employee_id = s.employee_id
    GROUP BY e.employee_id, e.employee_name
) AS employee_sales

UNION

SELECT manager_id AS employee_id, manager_name AS employee_name, total_sales
FROM (
    SELECT m.manager_id, m.manager_name, SUM(s.sales_amount) AS total_sales
    FROM managers m
    JOIN sales s ON m.manager_id = s.manager_id
    GROUP BY m.manager_id, m.manager_name
) AS manager_sales;

This combines employee and manager sales data.

7. Write a query using UNION to combine results from two tables with different data types in corresponding columns. How do you ensure compatibility?

To ensure compatibility with different data types in a UNION:

SELECT column1, CAST(column2 AS VARCHAR) AS column2
FROM table1
UNION
SELECT column1, column2
FROM table2;

This casts column2 to match data types.

8. Write a query using UNION to combine results from two tables, incorporating JOIN operations in the process.

To combine results using UNION with JOIN operations:

SELECT a.id, a.name, b.value
FROM table1 a
JOIN table2 b ON a.id = b.id
UNION
SELECT c.id, c.name, d.value
FROM table3 c
JOIN table4 d ON c.id = d.id;

This combines results from joined tables.

9. Write a query using UNION to combine results from two tables, utilizing subqueries for one or both tables.

Using UNION with subqueries:

SELECT employee_id, employee_name, department
FROM (
    SELECT employee_id, employee_name, department
    FROM employees
    WHERE department = 'Sales'
) AS sales_dept
UNION
SELECT employee_id, employee_name, department
FROM (
    SELECT employee_id, employee_name, department
    FROM employees
    WHERE department = 'Marketing'
) AS marketing_dept;

This combines results from subqueries for different departments.

10. Describe how the execution plan of a UNION query can affect performance. What tools or methods would you use to analyze and optimize it?

The execution plan of a UNION query affects performance due to sorting and merging operations. UNION ALL is generally faster as it skips duplicate removal. To analyze and optimize, use:

• EXPLAIN or EXPLAIN PLAN: Provides a breakdown of query execution.
• Query Profiling Tools: Tools like MySQL’s SHOW PROFILE or PostgreSQL’s EXPLAIN ANALYZE offer insights into resource usage.
• Indexing: Improves performance by reducing retrieval and sorting time.
• Query Optimization: Simplifying SELECT statements or using UNION ALL can enhance performance.
• Database Configuration: Tuning parameters like work_mem in PostgreSQL or sort_buffer_size in MySQL helps manage resources.