How to Use the UNION Operator in MySQL Database

I remember the exact moment UNION clicked for me — I was combining “active customers” and “recently churned customers” from two structurally similar but separately maintained tables into one report, and doing it with two separate queries and application-side merging felt clumsy. One UNION statement replaced about 30 lines of glue code. Since then it’s become one of my go-to tools whenever data that’s logically “the same kind of thing” lives in more than one place — different tables, or the same table queried under different conditions that don’t collapse into a single WHERE clause.

What UNION Actually Does

UNION combines the result sets of two or more SELECT statements into a single result set, stacking rows vertically rather than joining them side by side like a JOIN does.

graph TD
A[SELECT Query 1] --> C[UNION Combines Result Sets]
B[SELECT Query 2] --> C
C --> D{DISTINCT or ALL?}
D -->|UNION - default DISTINCT| E[Deduplicate Rows]
D -->|UNION ALL| F[Keep All Rows Including Duplicates]
E --> G[Final Result Set]
F --> G

Basic Requirements for UNION

For UNION to work, each SELECT statement must:
– Return the same number of columns
– Have compatible data types in corresponding column positions
– Only the first SELECT’s column names/aliases are used in the final result set

1. Write Multiple SELECT Queries:

Write the individual SELECT queries that you want to combine using the UNION operator. Make sure that the number of columns and their data types match in all the SELECT queries.

SELECT column1, column2 FROM table1 WHERE condition1
UNION
SELECT column1, column2 FROM table2 WHERE condition2
UNION
SELECT column1, column2 FROM table3 WHERE condition3;

SELECT column1, column2 FROM table1 WHERE condition1
UNION
SELECT column1, column2 FROM table2 WHERE condition2
UNION
SELECT column1, column2 FROM table3 WHERE condition3;

2. Use UNION to Combine Results:

In this example, the results of three separate SELECT queries are combined using the UNION operator. Each SELECT query should have the same number of columns and compatible data types.

3. Use UNION ALL for Duplicate Entries:

If you want to include duplicate entries in the result set, use UNION ALL instead of UNION.

SELECT column1, column2 FROM table1 WHERE condition1
UNION ALL
SELECT column1, column2 FROM table2 WHERE condition2;

SELECT column1, column2 FROM table1 WHERE condition1
UNION ALL
SELECT column1, column2 FROM table2 WHERE condition2;

4. ORDER BY Clause with UNION:

You can use the ORDER BY clause at the end of the entire UNION query to sort the combined result set.

(SELECT column1, column2 FROM table1 WHERE condition1)
UNION
(SELECT column1, column2 FROM table2 WHERE condition2)
ORDER BY column1;

(SELECT column1, column2 FROM table1 WHERE condition1)
UNION
(SELECT column1, column2 FROM table2 WHERE condition2)
ORDER BY column1;

Important Notes:

• The number of columns and their data types must be consistent across all SELECT queries.
• UNION eliminates duplicate rows from the combined result set. Use UNION ALL if you want to include duplicates.
• UNION and UNION ALL work with vertically aligned columns. The column positions in the first SELECT query will match with the positions in the subsequent SELECT queries.

Example:

Let’s assume you have two tables customers and employees. You want to combine the names from both tables into a single list:

SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM employees;

SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM employees;

This query will return a list of unique names from both tables. If you want to include duplicates, use UNION ALL instead of UNION.