Difference between UNION and UNION ALL in SQL

The primary difference between UNION and UNION ALL lies in how they handle duplicate rows when combining the results from two or more SELECT statements.

UNION:

Definition: Combines the result sets of two or more SELECT statements and removes duplicate records from the result.

Use Case: When you need to merge data from multiple queries but only want unique records in the final result set.

Processing: The UNION operator performs a sort and comparison operation to filter out duplicates. This makes it more resource-intensive, as it needs to eliminate rows that appear more than once.

---

UNION ALL:📦

Definition: Combines the result sets of two or more SELECT statements and includes all records, even if they are duplicates.

Use Case: When you want to merge data from multiple queries and allow duplicate records to be returned in the result set.

Processing: Since UNION ALL simply appends the result sets without any duplicate removal or sorting, it is faster and requires fewer system resources compared to UNION.

---

Syntax:

UNION Syntax:

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

UNION ALL Syntax:

SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;

Example:

Suppose we have two tables employees_2023 and employees_2024:

Step 1: Create Tables

CREATE TABLE employees_2023 (
emp_id INT,
emp_name VARCHAR(50)
);

CREATE TABLE employees_2024 (
emp_id INT,
emp_name VARCHAR(50)
);

Step 2: Insert Sample Data

-- Insert data into employees_2023
INSERT INTO employees_2023 (emp_id, emp_name)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Insert data into employees_2024
INSERT INTO employees_2024 (emp_id, emp_name)
VALUES
(2, 'Bob'),
(4, 'David'),
(5, 'Eve');

-- Table: employees_2023

emp_id	emp_name
1	Alice
2	Bob
3	Charlie

-- Table: employees_2024

emp_id	emp_name
2	Bob
4	David
5	Eve

Using UNION:

SELECT emp_id, emp_name FROM employees_2023
UNION
SELECT emp_id, emp_name FROM employees_2024;

The result will remove duplicates (in this case, Bob appears only once):

emp_id	emp_name
1	Alice
2	Bob
3	Charlie
4	David
5	Eve

Using UNION ALL:

SELECT emp_id, emp_name FROM employees_2023
UNION ALL
SELECT emp_id, emp_name FROM employees_2024;

The result will include duplicates:

emp_id	emp_name
1	Alice
2	Bob
3	Charlie
2	Bob
4	David
5	Eve

---

Performance Comparison:

UNION involves a deduplication step, which adds overhead. The system needs to sort the result sets and then remove duplicates, which can make it slower.

UNION ALL is faster because it does not involve removing duplicates. It simply merges all results from the queries.

In cases where you know your result sets will have no duplicates, you should prefer UNION ALL for performance reasons. Use UNION only when eliminating duplicates is essential for your query.

---

Common Interview Questions Regarding UNION vs UNION ALL:

1. What is the difference between UNION and UNION ALL?

Answer: The UNION operator removes duplicate rows from the result set, while UNION ALL includes all rows, including duplicates. UNION is slower due to the extra work needed to remove duplicates, whereas UNION ALL is faster since it simply appends the result sets without checking for duplicates.

1. When would you use UNION over UNION ALL?

Answer: You should use UNION when you want to ensure that the result set contains only unique rows, meaning duplicates need to be eliminated. If you don't care about duplicates and need better performance, use UNION ALL.

1. Can UNION and UNION ALL be used with different numbers of columns in the SELECT statements?

Answer: No, both UNION and UNION ALL require the same number of columns in each SELECT statement and that these columns should have compatible data types. If the number of columns or data types are mismatched, you will encounter an error.

1. Which operator would you recommend for merging large datasets, UNION or UNION ALL?

Answer: If performance is a priority and you know that there are no duplicates or you're okay with duplicates, you should use UNION ALL because it is faster. If you need to ensure uniqueness in the final result set, use UNION, but be aware that it will be slower due to the deduplication process.

1. How does the sorting operation in UNION impact performance?

Answer: The sorting operation in UNION can significantly impact performance, especially for large datasets. Since UNION must sort the combined result set to remove duplicates, this step can consume considerable resources (memory, CPU). UNION ALL avoids this overhead by not performing any sorting or deduplication, making it faster.

1. How can you simulate the behavior of UNION using UNION ALL?

Answer: You can simulate the behavior of UNION using UNION ALL by adding a DISTINCT clause to eliminate duplicates after the union:

SELECT DISTINCT * FROM (
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2
) AS combined_result;

However, this may not improve performance compared to using UNION directly, as you are still performing a deduplication step.

1. Can UNION be used with ORDER BY?

Answer: Yes, UNION and UNION ALL can be used with ORDER BY, but the ORDER BY clause can only be applied to the final result set, not to individual SELECT queries. For example:

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2
ORDER BY column_name;

1. How does database indexing affect UNION and UNION ALL performance?

Answer: Indexing can improve the performance of both UNION and UNION ALL by speeding up data retrieval. However, for UNION, indexing does not eliminate the overhead of sorting and deduplication. In contrast, UNION ALL benefits more from indexing because it simply merges the results without additional processing.

1. What happens if you try to use UNION on two queries that don't return the same number of columns?

Answer: If the number of columns differs between the two queries in a UNION or UNION ALL operation, you will get an error. Both queries must return the same number of columns with compatible data types.

1. Is it possible to combine UNION and UNION ALL in the same query?

Answer: Yes, you can combine UNION and UNION ALL in the same query by chaining them, but the rules for each apply separately. For example:

SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2
UNION
SELECT column_list FROM table3;

In this case, the result set from table1 and table2 will include duplicates, but the final result after the UNION with table3 will be deduplicated.