MySQL - UNION vs UNION ALL

MySQL: UNION vs. UNION ALL

In SQL, the UNION and UNION ALL operators are used to combine the result sets of two or more SELECT statements into a single result set. While they serve a similar purpose, they have a critical difference related to handling duplicate rows, which has significant implications for performance and results.

For these operators to work, the SELECT statements being combined must adhere to the following rules:

• Each SELECT statement must have the same number of columns.
• The corresponding columns must have compatible data types (e.g., you can union an INT with a BIGINT, or a VARCHAR with a CHAR).
• The columns in each SELECT statement must be in the same order.

The MySQL UNION Operator

The UNION operator combines result sets and then removes duplicate rows. It effectively performs a SELECT DISTINCT on the combined set, ensuring every row in the final result is unique.

Syntax

The basic syntax is as follows:

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

The MySQL UNION ALL Operator

The UNION ALL operator also combines result sets, but it does not remove duplicate rows. It simply appends the rows from the second result set to the first, resulting in a faster but potentially redundant output.

Syntax

The syntax is nearly identical:

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

Key Differences and Performance

Feature	UNION	UNION ALL
Duplicate Rows	Removes duplicates	Includes all duplicates
Performance	Slower	Faster
Use Case	When you need only unique records from multiple sources.	When duplicates are acceptable or known not to exist.

Performance Insight: UNION is slower because the database must perform an extra step to identify and eliminate duplicate rows. This often involves sorting the combined result set and scanning for duplicates. UNION ALL skips this overhead entirely. Best Practice: Always use UNION ALL unless you have a specific requirement to remove duplicates.

Practical Example: Comparing UNION and UNION ALL

Let’s create two tables to store course enrollments for full-time and part-time students. Note that some students may be enrolled in both.

CREATE TABLE full_time_students (
    student_id INT NOT NULL,
    student_name VARCHAR(50) NOT NULL,
    course_name VARCHAR(50) NOT NULL
);

CREATE TABLE part_time_students (
    student_id INT NOT NULL,
    student_name VARCHAR(50) NOT NULL,
    course_name VARCHAR(50) NOT NULL
);

INSERT INTO full_time_students VALUES
(101, 'Alice', 'Calculus I'),
(102, 'Bob', 'History 101'),
(103, 'Charlie', 'Physics I');

INSERT INTO part_time_students VALUES
(201, 'David', 'English Lit'),
(102, 'Bob', 'History 101'), -- Duplicate record
(202, 'Eve', 'Art History');

Combining with UNION

To get a unique list of all student enrollments, we use UNION. Notice that Bob’s duplicate enrollment is removed.

SELECT student_id, student_name, course_name FROM full_time_students
UNION
SELECT student_id, student_name, course_name FROM part_time_students;

The result contains only distinct rows:

student_id	student_name	course_name
101	Alice	Calculus I
102	Bob	History 101
103	Charlie	Physics I
201	David	English Lit
202	Eve	Art History

Combining with UNION ALL

To get a complete list of all enrollments, including duplicates, we use UNION ALL. This might be useful for generating a full class roster.

SELECT student_id, student_name, course_name FROM full_time_students
UNION ALL
SELECT student_id, student_name, course_name FROM part_time_students;

The result includes all six rows from both tables:

student_id	student_name	course_name
101	Alice	Calculus I
102	Bob	History 101
103	Charlie	Physics I
201	David	English Lit
102	Bob	History 101
202	Eve	Art History