UNION & UNION ALL

Introduction

So far, every query you have written has produced results from a single SELECT statement. But sometimes you need to combine the results of two separate SELECT queries into one list — for example, merging "current students" and "alumni" into a single combined roster.

This is exactly what the UNION and UNION ALL operators are for. They stack the results of two or more queries on top of each other, producing one final result set.

What is UNION?

UNION combines the result sets of two or more SELECT statements into a single result set, stacking the rows from the second query underneath the rows from the first. By default, UNION also removes any duplicate rows from the combined output.

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

Column Rules

UNION does not work on just any two queries — both SELECT statements must be "union-compatible":

Worked Example: Students and Alumni

Suppose a school keeps two separate tables: "students" for people currently enrolled, and "alumni" for people who have graduated. A newsletter needs one combined list of every name and email, past and present.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE alumni (
    id INT PRIMARY KEY,
    full_name VARCHAR(50),
    contact_email VARCHAR(100)
);

INSERT INTO students VALUES
    (1, 'Alex', 'alex@school.com'),
    (2, 'Priya', 'priya@school.com');

INSERT INTO alumni VALUES
    (1, 'Sam', 'sam@alumni.com'),
    (2, 'Priya', 'priya@school.com');

Notice that "Priya" appears in both tables with the exact same email — a realistic scenario if a student's record was copied over after graduation. Now combine both lists with UNION:

SELECT name, email FROM students
UNION
SELECT full_name, contact_email FROM alumni;

+-------+---------------------+
| name  | email               |
+-------+---------------------+
| Alex  | alex@school.com     |
| Priya | priya@school.com    |
| Sam   | sam@alumni.com      |
+-------+---------------------+

Even though "Priya" existed in both source tables, she appears only once in the final result — UNION automatically removed the duplicate row.

UNION ALL

UNION ALL combines result sets exactly like UNION, but it keeps every row, including exact duplicates. Because it skips the extra step of scanning for and removing duplicates, UNION ALL is generally faster than UNION, especially on large result sets.

SELECT name, email FROM students
UNION ALL
SELECT full_name, contact_email FROM alumni;

+-------+---------------------+
| name  | email               |
+-------+---------------------+
| Alex  | alex@school.com     |
| Priya | priya@school.com    |
| Sam   | sam@alumni.com      |
| Priya | priya@school.com    |
+-------+---------------------+

This time "Priya" shows up twice, once from each table, because UNION ALL does not check for or remove duplicates.

UNION vs UNION ALL

Ordering Combined Results

An ORDER BY clause used with UNION applies to the entire combined result set, and must go at the very end — after the last SELECT statement, not attached to an individual query.

SELECT name, email FROM students
UNION
SELECT full_name, contact_email FROM alumni
ORDER BY name;

+-------+---------------------+
| name  | email               |
+-------+---------------------+
| Alex  | alex@school.com     |
| Priya | priya@school.com    |
| Sam   | sam@alumni.com      |
+-------+---------------------+

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

UNION and UNION ALL let you merge the results of multiple SELECT queries into a single, unified result set — useful whenever related data is split across tables, such as current students and alumni.

In this lesson, you learned the difference between UNION and UNION ALL, the column-matching rules they require, and how to sort a combined result. Next, you will learn about views — saved queries that behave like virtual tables.