MySQL UNION / UNION ALL

UNION combines the results of multiple SELECT statements into one result.

SELECT name
FROM students
UNION
SELECT name
FROM teachers;

The names from both queries are combined into a single result set.

UNION is useful when information comes from different queries or tables but needs to be displayed as one result.

• Combine current and previous records.
• Combine students from different tables.
• Combine customer lists.
• Combine employee and trainer names.
• Combine data from different categories.
• Create combined reports.

SELECT column1, column2
FROM table1

UNION

SELECT column1, column2
FROM table2;

Each SELECT statement produces a result, and UNION combines those results.

Suppose we have two tables:

online_students
----------------
name

offline_students
----------------
name

We can combine their names:

SELECT name
FROM online_students

UNION

SELECT name
FROM offline_students;

UNION removes duplicate rows from the final result.

Suppose the first query returns:

Amit
Priya
Rahul

And the second query returns:

Rahul
Neha
Amit

Using UNION gives:

Amit
Priya
Rahul
Neha

Amit and Rahul appear only once.

UNION ALL combines result sets without removing duplicate rows.

SELECT name
FROM online_students

UNION ALL

SELECT name
FROM offline_students;

If the same name appears in both tables, it can appear multiple times in the final result.

Each SELECT statement in a UNION must return the same number of columns.

Correct:

SELECT name, city
FROM students

UNION

SELECT name, city
FROM teachers;

Incorrect:

SELECT name, city
FROM students

UNION

SELECT name
FROM teachers;

The second query has only one column.

Corresponding columns should have compatible data types.

For example:

SELECT name, age
FROM students

UNION

SELECT name, age
FROM teachers;

Both queries return a name column and an age column.

Different but compatible numeric types can generally be combined, but incompatible expressions may produce errors or implicit conversions.

The column names of a UNION result are generally taken from the first SELECT statement.

SELECT
    name AS person_name
FROM students

UNION

SELECT
    name AS employee_name
FROM employees;

The resulting column will use the name from the first SELECT expression.

Each SELECT statement can have its own WHERE condition.

SELECT name
FROM students
WHERE city = 'Patna'

UNION

SELECT name
FROM teachers
WHERE city = 'Patna';

This combines students and teachers from Patna.

UNION can combine results from different tables.

SELECT
    name,
    city
FROM students

UNION

SELECT
    name,
    city
FROM employees;

Both SELECT statements return two compatible columns.

SELECT
    name,
    city
FROM students

UNION ALL

SELECT
    name,
    city
FROM employees;

Every row from both queries is included, including duplicate rows.

You can combine more than two SELECT statements.

SELECT name
FROM students

UNION

SELECT name
FROM teachers

UNION

SELECT name
FROM employees;

The results from all three queries are combined.

SELECT name
FROM online_students

UNION ALL

SELECT name
FROM offline_students

UNION ALL

SELECT name
FROM weekend_students;

This keeps every row from all three tables.

ORDER BY can sort the final combined result.

SELECT name
FROM students

UNION

SELECT name
FROM teachers

ORDER BY name ASC;

The ORDER BY clause is applied to the final UNION result.

LIMIT can restrict the final combined result.

SELECT name
FROM students

UNION

SELECT name
FROM teachers

LIMIT 10;

This returns a maximum of 10 rows from the final result.

When using UNION, ORDER BY normally belongs to the final combined result.

SELECT name
FROM students

UNION

SELECT name
FROM teachers

ORDER BY name;

If an individual SELECT needs its own ORDER BY or LIMIT before UNION, parentheses can be used around the SELECT query where appropriate.

Expressions can be used in UNION queries as long as the corresponding result columns are compatible.

SELECT
    name,
    fee * 1.10 AS amount
FROM students

UNION ALL

SELECT
    name,
    salary * 1.10 AS amount
FROM employees;

Both SELECT statements return two columns.

You can add a fixed value to identify where each row came from.

SELECT
    name,
    'Student' AS person_type
FROM students

UNION ALL

SELECT
    name,
    'Teacher' AS person_type
FROM teachers;

This makes it easy to identify the source category in the combined result.

UNION can combine results from different queries representing different periods.

SELECT
    student_id,
    amount,
    'January' AS month_name
FROM payments
WHERE payment_date >= '2026-01-01'
AND payment_date < '2026-02-01'

UNION ALL

SELECT
    student_id,
    amount,
    'February' AS month_name
FROM payments
WHERE payment_date >= '2026-02-01'
AND payment_date < '2026-03-01';

This creates a combined January and February result.

Each SELECT statement in a UNION can contain JOINs.

SELECT
    s.name,
    c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id

UNION

SELECT
    t.name,
    c.course_name
FROM teachers t
INNER JOIN courses c
ON t.course_id = c.id;

Each query creates its own result before UNION combines them.

Aggregate queries can also be combined using UNION.

SELECT
    'Students' AS category,
    COUNT(*) AS total
FROM students

UNION ALL

SELECT
    'Teachers' AS category,
    COUNT(*) AS total
FROM teachers;

This creates a small summary report.

UNION removes duplicate result rows, not merely duplicate values from one particular column.

For example:

SELECT name, city
FROM students

UNION

SELECT name, city
FROM teachers;

If both name and city are identical in two result rows, UNION keeps one copy.

If the names are the same but the cities are different, both rows can remain.

UNION is useful when you want to place similar results one below another.

• Using different numbers of columns.
• Using incompatible column expressions.
• Forgetting that UNION removes duplicate rows.
• Using UNION when duplicate rows are required.
• Using UNION ALL when duplicate removal is required.
• Putting ORDER BY in the wrong place.
• Using different column meanings in corresponding positions.

NULL can be used when one result needs a placeholder for a column that another result provides.

SELECT
    name,
    city,
    course_id
FROM students

UNION ALL

SELECT
    name,
    city,
    NULL AS course_id
FROM teachers;

Both SELECT statements return three columns.

A typical UNION query can follow this structure:

SELECT columns
FROM table1
WHERE condition

UNION ALL

SELECT columns
FROM table2
WHERE condition

ORDER BY column_name
LIMIT 10;

Each SELECT can have its own filtering logic, while ORDER BY and LIMIT can be applied to the combined result.

Suppose a system stores students and employees in separate tables.

SELECT
    name,
    city,
    'Student' AS person_type
FROM students

UNION ALL

SELECT
    name,
    city,
    'Employee' AS person_type
FROM employees

ORDER BY name;

This produces one combined people report while keeping the source type.

CREATE TABLE online_students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

CREATE TABLE offline_students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

INSERT INTO online_students
(name, city)
VALUES
('Amit Kumar', 'Patna'),
('Priya Singh', 'Gaya'),
('Rahul Sharma', 'Patna');

INSERT INTO offline_students
(name, city)
VALUES
('Rahul Sharma', 'Patna'),
('Neha Kumari', 'Gaya'),
('Ravi Kumar', 'Delhi');

-- UNION removes duplicate rows
SELECT
    name,
    city
FROM online_students

UNION

SELECT
    name,
    city
FROM offline_students;

-- UNION ALL keeps duplicate rows
SELECT
    name,
    city
FROM online_students

UNION ALL

SELECT
    name,
    city
FROM offline_students;

-- Add a source/category column
SELECT
    name,
    city,
    'Online' AS mode
FROM online_students

UNION ALL

SELECT
    name,
    city,
    'Offline' AS mode
FROM offline_students
ORDER BY name;

-- Count students from both sources
SELECT
    'Online' AS mode,
    COUNT(*) AS total_students
FROM online_students

UNION ALL

SELECT
    'Offline' AS mode,
    COUNT(*) AS total_students
FROM offline_students;

This example demonstrates UNION, UNION ALL, duplicate handling, category columns, ORDER BY, and aggregate queries.