Difference between UNION and UNION ALL in SQL

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.

---