#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

UNION vs UNION ALL in MySQL: Differences and Examples

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;
namephonecourse
Aditi9810000001Python
Rohan9810000002SQL
Yash9810000003Excel

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;
namephonecourse
Aditi9810000001Python
Rohan9810000002SQL
Aditi9810000001Python
Rohan9810000002SQL
Yash9810000003Excel
Aditi9810000001Python

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;
courseleads
Python3
SQL2
Excel1

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

UNIONUNION ALL
Duplicate rowsRemoved (same as UNION DISTINCT)Kept
Extra workBuilds a temporary result to find duplicatesStreams rows straight through
Typical useDistinct lists: emails, IDs, unique contactsTotals, logs, appending monthly tables
Row orderUndefined 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;
channelnamecourse
onlineAditiPython
walk-inAditiPython
walk-inAditiPython
onlineRohanSQL
walk-inRohanSQL
onlineYashExcel

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);
namecourse
AditiPython
AditiPython

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.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →