跳至主要内容

MySQL UNION ALL Clause

The `UNION ALL` clause in MySQL is used to combine the results of two or more `SELECT` statements. Unlike `UNION`, it includes duplicate rows in the final result set.

Usage

The `UNION ALL` clause is used when you need to combine results from multiple queries without removing duplicate entries. It is particularly useful for aggregating data sets where duplicates are meaningful and should be preserved.

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

In this syntax, `UNION ALL` merges the results of both `SELECT` statements, including any duplicate rows.

Examples

1. Basic UNION ALL

SELECT name FROM students
UNION ALL
SELECT name FROM alumni;

This example combines names from both `students` and `alumni` tables, including duplicates if any names appear in both tables.

2. UNION ALL with Different Columns

SELECT id, name FROM employees
UNION ALL
SELECT employee_id, full_name FROM contractors;

Here, the `UNION ALL` merges the `id` and `name` columns from `employees` with `employee_id` and `full_name` from `contractors`. Both queries must have the same number of columns in the same order, and their data types should be compatible to avoid errors.

3. Using UNION ALL with Conditions

SELECT product_id, description FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_id, description FROM discontinued_products WHERE category = 'Electronics';

This example combines products and discontinued products within the 'Electronics' category, retaining all duplicates.

4. Using UNION ALL with ORDER BY

SELECT id, name FROM employees
UNION ALL
SELECT employee_id, full_name FROM contractors
ORDER BY name;

This example demonstrates applying `ORDER BY` to the final result set to ensure the combined results are sorted by name.

Tips and Best Practices

  • Ensure column compatibility. All `SELECT` statements must have the same number of columns with compatible data types to avoid errors.
  • Preserve meaningful duplicates. Use `UNION ALL` when duplicate rows are beneficial to your analysis or reporting needs.
  • Optimize query performance. Be aware that using `UNION ALL` with large datasets can impact performance; consider indexing critical columns. Utilize the `EXPLAIN` statement to analyze the query plan for optimization opportunities.
  • Use explicit ordering. If order matters, apply `ORDER BY` to the final result set, not individual queries within the `UNION ALL`.
  • Error handling. Ensure columns in all `SELECT` statements have matching counts and compatible data types to prevent runtime errors.