A training centre keeps walk-in enquiries in one table and website enquiries in another. The counsellor wants a single call list. Stacking the two result sets is a job for UNION or UNION ALL. The only difference between them is what happens to identical rows: UNION removes duplicates, UNION ALL keeps every row. That one difference affects both the answer and the speed.
The two tables
CREATE TABLE delhi_leads (name VARCHAR(20), phone VARCHAR(12), course VARCHAR(15));
CREATE TABLE online_leads (full_name VARCHAR(20), mobile VARCHAR(12), course VARCHAR(15), source VARCHAR(10));
INSERT INTO delhi_leads VALUES
('Aditi','9810000001','Python'), ('Rohan','9810000002','SQL'), ('Aditi','9810000001','Python');
INSERT INTO online_leads VALUES
('Rohan','9810000002','SQL','Instagram'), ('Yash','9810000003','Excel','Google'),
('Aditi','9810000001','Python','Google');
Aditi filled the walk-in form twice and the website form once. Rohan appears in both tables. The phone numbers are dummy values.
UNION: one row per distinct result
SELECT name, phone, course FROM delhi_leads
UNION
SELECT full_name, mobile, course FROM online_leads;
| name | phone | course |
|---|---|---|
| Aditi | 9810000001 | Python |
| Rohan | 9810000002 | SQL |
| Yash | 9810000003 | Excel |
Six input rows became three. The duplicates are removed across the combined result, so even the repeat inside delhi_leads went. Note that the column names come from the first SELECT.
UNION ALL: every row, as it is
SELECT name, phone, course FROM delhi_leads
UNION ALL
SELECT full_name, mobile, course FROM online_leads;
| name | phone | course |
|---|---|---|
| Aditi | 9810000001 | Python |
| Rohan | 9810000002 | SQL |
| Aditi | 9810000001 | Python |
| Rohan | 9810000002 | SQL |
| Yash | 9810000003 | Excel |
| Aditi | 9810000001 | Python |
All six rows come back. For a call list that is wrong. For counting enquiries, it is exactly right:
SELECT course, COUNT(*) AS leads
FROM (
SELECT course FROM delhi_leads
UNION ALL
SELECT course FROM online_leads
) t
GROUP BY course
ORDER BY leads DESC, course;
| course | leads |
|---|---|
| Python | 3 |
| SQL | 2 |
| Excel | 1 |
With UNION here, each course would collapse to a single row before counting, and every count would be 1. Choose the operator based on what a duplicate means in your question, not by habit.
Side by side
| UNION | UNION ALL | |
|---|---|---|
| Duplicate rows | Removed (same as UNION DISTINCT) | Kept |
| Extra work | Builds a temporary result to find duplicates | Streams rows straight through |
| Typical use | Distinct lists: emails, IDs, unique contacts | Totals, logs, appending monthly tables |
| Row order | Undefined for both; add an ORDER BY at the end | |
You can see the extra work in the query plan:
EXPLAIN FORMAT=TREE SELECT name FROM delhi_leads UNION SELECT full_name FROM online_leads;
-> Table scan on <union temporary> (cost=2.13..4.28 rows=6)
-> Union materialize with deduplication (cost=1.7..1.7 rows=6)
-> Table scan on delhi_leads (cost=0.55 rows=3)
-> Table scan on online_leads (cost=0.55 rows=3)
EXPLAIN FORMAT=TREE SELECT name FROM delhi_leads UNION ALL SELECT full_name FROM online_leads;
-> Append (cost=1.1 rows=6)
-> Stream results (cost=0.55 rows=3)
-> Table scan on delhi_leads (cost=0.55 rows=3)
-> Stream results (cost=0.55 rows=3)
-> Table scan on online_leads (cost=0.55 rows=3)
"Materialize with deduplication" is the cost UNION adds. On six rows it does not matter. On millions of rows, that temporary table can spill to disk. If you know the branches cannot overlap (for example, WHERE year = 2025 and WHERE year = 2026), use UNION ALL.
Rules both operators share
Same number of columns
SELECT name, phone FROM delhi_leads
UNION
SELECT full_name, mobile, course FROM online_leads;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
Columns match by position, not by name
MySQL pairs the first column with the first, and so on. If one branch lists phone, name, names land in the phone column without any error. Types are also merged loosely: SELECT 1 AS n UNION SELECT 'one' runs and returns 1 and one in one text column. Keep the types consistent yourself.
Label where each row came from
SELECT 'walk-in' AS channel, name, course FROM delhi_leads
UNION ALL
SELECT 'online', full_name, course FROM online_leads
ORDER BY name, channel;
| channel | name | course |
|---|---|---|
| online | Aditi | Python |
| walk-in | Aditi | Python |
| walk-in | Aditi | Python |
| online | Rohan | SQL |
| walk-in | Rohan | SQL |
| online | Yash | Excel |
The final ORDER BY sorts the combined result and must use the first branch's column names. Adding a constant column also changes what counts as a duplicate: with UNION, Aditi's walk-in and online rows would now both survive, because they differ in channel.
ORDER BY or LIMIT inside one branch needs parentheses
(SELECT name, course FROM delhi_leads ORDER BY name LIMIT 1)
UNION ALL
(SELECT full_name, course FROM online_leads ORDER BY full_name LIMIT 1);
| name | course |
|---|---|
| Aditi | Python |
| Aditi | Python |
Related operators
MySQL 8.0.31 added INTERSECT (rows in both) and EXCEPT (rows in the first but not the second). On older versions, use JOIN or NOT EXISTS. To combine tables side by side, matching on a key, you need a join, not a union. See MySQL joins explained. Also read join vs subquery performance, and aggregate functions for counting a union's output.