Understanding MySQL UNION: Combining Rows Across Tables

Introduction

MySQL’s UNION operator lets you stack result sets from separate SELECT statements on top of each other, creating a single list of rows. Unlike joins, which combine columns side‑by‑side, UNION merges rows vertically.

Basic Syntax

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

• Both SELECT statements must return the same number of columns.
• Corresponding columns should have compatible data types.

UNION vs UNION ALL

• UNION (the default) behaves like SELECT DISTINCT; it removes duplicate rows.
• UNION ALL keeps every row, including duplicates, and is faster because it skips the distinct step.

Practical Example

1. First query – pull age and gender from employee_demographics.
2. Second query – pull first_name and last_name from employee_salary.
3. Running the UNION shows two separate blocks of data, but mixing unrelated columns (age/gender with names) produces meaningless output.

Keeping Data Consistent

To make the UNION useful, select the same type of data from each table, e.g., first_name and last_name from both employee_demographics and employee_salary.

SELECT first_name, last_name FROM employee_demographics
UNION
SELECT first_name, last_name FROM employee_salary;

• The result contains each unique name once because UNION removes duplicates.
• Using UNION ALL would list every occurrence, showing duplicates.

Adding Labels and Filters

The instructor demonstrated a real‑world scenario for a parks department that wants to identify: - Employees older than a certain age. - Employees earning more than $70,000.

SELECT first_name, last_name, 'Old Man'   AS category
FROM employee_demographics
WHERE age > 40 AND gender = 'Male'
UNION
SELECT first_name, last_name, 'Old Lady' AS category
FROM employee_demographics
WHERE age > 40 AND gender = 'Female'
UNION
SELECT first_name, last_name, 'Highly Paid Employee' AS category
FROM employee_salary
WHERE salary > 70000;

• Each SELECT returns the same two name columns plus a descriptive label.
• The three UNIONs combine all categories into one result set.

Ordering the Combined Results

To view the data in a logical order, an ORDER BY clause can be added after the final UNION:

... 
ORDER BY first_name, last_name;

This makes it easy to see which employees appear in multiple categories (e.g., someone who is both an "Old Man" and a "Highly Paid Employee").

Key Takeaways

• Use UNION when you need a distinct list of rows from multiple queries.
• Use UNION ALL when you want every row, including duplicates.
• Ensure each SELECT returns the same columns and compatible data types.
• Labels (or constant strings) can be added to each query to identify the source or category of each row.
• An ORDER BY clause after the final UNION helps spot overlaps and prioritize results.

UNION is a powerful tool for vertically merging query results; by aligning column structures, choosing between DISTINCT (UNION) and inclusive (UNION ALL) behavior, and optionally labeling rows, you can create clear, consolidated reports that highlight overlapping criteria without needing to run separate queries.