VOOZH about

URL: https://www.geeksforgeeks.org/mysql/mysql-union-all-operator/

⇱ MySQL UNION ALL Operator - GeeksforGeeks

  • Courses
  • Tutorials
  • Interview Prep

MySQL UNION ALL Operator

Last Updated : 26 Mar, 2026

The UNION ALL operator in MySQL combines the results of multiple SELECT statements while retaining duplicate rows. It is faster and more efficient when duplicate removal is not required.

  • Combines result sets from two or more SELECT statements.
  • Retains all duplicate records in the final output.
  • Performs better than UNION since it skips duplicate elimination.

Syntax:

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
  • Retrieves the same columns from both queries.
  • Combines results using UNION ALL, including duplicates.
  • Returns a single combined result set.

Note: Each SELECT statement can include optional WHERE conditions.

Working with the UNION ALL Operator

It demonstrates how the MySQL UNION ALL operator is used to combine results from multiple queries. First, we create a demo table on which the UNION ALL operator will be used:

👁 Screenshot-2026-03-26-113224
employees Table

Example 1: Return Single Field using UNION ALL Operator

This example retrieves a combined list of employee names based on different conditions. It includes duplicate values if they satisfy both conditions.

Query:

SELECT name FROM employees 
WHERE department = 'HR'
UNION ALL
SELECT name FROM employees 
WHERE position = 'Developer';

Output:

👁 Screenshot-2026-03-26-114303
  • Combines names from both conditions into a single result.
  • Includes duplicate names if they appear in both queries.

Example 2: UNION ALL Operator with ORDER BY Clause

This example combines filtered data and sorts the final result. It demonstrates ordering after merging results.

Query:

SELECT name, position FROM employees 
WHERE department = 'HR'
UNION ALL
SELECT name, position FROM employees 
WHERE position = 'Developer'
ORDER BY position;

Output:

👁 Screenshot-2026-03-26-114456
  • Combines filtered records from both queries.
  • Sorts the final result using ORDER BY.

UNION ALL Vs UNION Operator

Here are some key differences between UNION ALL and UNION operator:

UNION ALL

UNION

Combines results of SELECT statements and includes all duplicates.

Combines results of SELECT statements and removes duplicate rows.

Faster, as it does not perform duplicate removal.

Slower, as it performs a distinct operation to remove duplicates.

Useful when you need to include every row from the combined queries, including duplicates.

Useful when you need to eliminate duplicate rows and only see unique results.

More efficient for large datasets with duplicates.

Less efficient due to the overhead of duplicate checking.

Advantages

The UNION ALL operator offers performance and efficiency benefits when working with combined datasets.

  • Better Performance: Faster than UNION as it does not remove duplicates.
  • Complete Data Inclusion: Retains all records, including duplicates, which is useful for full data analysis.
  • Simpler Query Logic: No need to add extra conditions to eliminate duplicates.
  • Efficient Resource Usage: Uses fewer system resources compared to UNION.
  • Suitable for Incremental Data: Ideal when combining datasets where all records must be preserved.
Comment
Article Tags:
Article Tags:

Explore