Free tutorials in Hindi & English Clean code examples with output Mobile friendly learning
MySQL + SQL · Lesson 75

UNION and UNION ALL in MySQL

What is UNION?

UNION combines the result sets of two or more SELECT statements into a single result set. It removes duplicate rows automatically. All SELECT statements must have the same number of columns with compatible data types.

Think of UNION like a mathematical union of sets — if the same student appears in two tables, UNION shows that student only once.

  • Number of columns in each SELECT must be equal.
  • Column data types must be compatible (e.g., both VARCHAR or both INT).
  • Column names in the final result come from the first SELECT.
  • Duplicate rows are removed.

What is UNION ALL?

UNION ALL also combines result sets of two or more SELECT statements, but it keeps all rows including duplicates. It is faster than UNION because it skips the duplicate-removal step.
  • All duplicate rows are kept.
  • Faster than UNION — no sorting or comparison needed to remove duplicates.
  • Use UNION ALL when you know duplicates won't exist or when you want to keep them.

UNION vs UNION ALL – Comparison Table

FeatureUNIONUNION ALL
DuplicatesRemoves duplicatesKeeps all duplicates
SpeedSlower (sorting required)Faster (no extra processing)
Use caseWhen unique rows neededWhen all rows needed (including duplicates)
Result rowsLess or equalAlways equal to sum of both
MemoryUses more (needs temp sort)Uses less

Syntax

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

-- UNION ALL Syntax
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Rule: Both SELECT statements must have the same number of columns and compatible data types.

SQL Examples with Output

Setup – Create Sample Tables

CREATE DATABASE school_db;
USE school_db;

-- Students of Section A
CREATE TABLE students_a (
  roll_no INT,
  name VARCHAR(60),
  city VARCHAR(40)
);

-- Students of Section B
CREATE TABLE students_b (
  roll_no INT,
  name VARCHAR(60),
  city VARCHAR(40)
);

INSERT INTO students_a VALUES
  (1, 'Aarav Sharma', 'Khurja'),
  (2, 'Priya Verma', 'Aligarh'),
  (3, 'Ravi Singh', 'Meerut');

INSERT INTO students_b VALUES
  (3, 'Ravi Singh', 'Meerut'),   -- duplicate row
  (4, 'Neha Gupta', 'Agra'),
  (5, 'Amit Yadav', 'Mathura');

Example 1: Using UNION

SELECT roll_no, name, city FROM students_a
UNION
SELECT roll_no, name, city FROM students_b;
Output (5 rows — Ravi Singh shown only once):
+----+--------------+---------+
| roll_no | name | city |
+---------+-------------+---------+
| 1 | Aarav Sharma | Khurja |
| 2 | Priya Verma | Aligarh |
| 3 | Ravi Singh | Meerut |
| 4 | Neha Gupta | Agra |
| 5 | Amit Yadav | Mathura |
+---------+-------------+---------+

Example 2: Using UNION ALL

SELECT roll_no, name, city FROM students_a
UNION ALL
SELECT roll_no, name, city FROM students_b;
Output (6 rows — Ravi Singh appears twice):
+---------+--------------+---------+
| roll_no | name | city |
+---------+--------------+---------+
| 1 | Aarav Sharma | Khurja |
| 2 | Priya Verma | Aligarh |
| 3 | Ravi Singh | Meerut |
| 3 | Ravi Singh | Meerut | <-- duplicate kept
| 4 | Neha Gupta | Agra |
| 5 | Amit Yadav | Mathura |
+---------+--------------+---------+

Example 3: UNION with ORDER BY

SELECT roll_no, name FROM students_a
UNION
SELECT roll_no, name FROM students_b
ORDER BY name;

Note: ORDER BY is written only once at the very end — it applies to the entire UNION result.

Example 4: UNION with different tables (fee records)

-- Students who paid fee in January OR February
SELECT student_id, 'January' AS month FROM fee_jan
UNION
SELECT student_id, 'February' AS month FROM fee_feb;

Real-World Use Cases

  • School ERP: Combine student lists from multiple sections (XII-A, XII-B, XII-C) for attendance report.
  • Fee System: Show all students who paid fee in any of the last 3 months.
  • Library System: Show all books issued today from multiple branches.
  • E-commerce: Show all products from two different seller tables.
  • Reporting: Combine data from archive table and current table to show full history.

Common Mistakes

  • Different column count: Both SELECT must have same number of columns. If not — ERROR: The used SELECT statements have a different number of columns.
  • Incompatible data types: Combining INT column with VARCHAR column — MySQL may auto-convert but result can be wrong.
  • Using ORDER BY in middle: ORDER BY belongs only at the end of the final UNION query, not inside each SELECT.
  • Using UNION when UNION ALL is better: If you know no duplicates exist, use UNION ALL for better performance.
  • Column names confusion: Final result uses column names from the FIRST SELECT, not the second.

Practice Tasks

  1. Create two tables: teachers_math and teachers_science, insert 3 rows each with 1 common teacher. Show UNION and UNION ALL output and explain the difference.
  2. Write a query using UNION ALL to combine students from 3 different class tables (class10, class11, class12) and count total rows using a subquery.
  3. Use UNION to find all unique cities from both students_a and students_b tables.
  4. Write a viva question: "What happens if column count is different in UNION?" — Answer it.

Summary

  • UNION = Combine + Remove duplicates (slower)
  • UNION ALL = Combine + Keep all rows (faster)
  • Both require same number of columns with compatible types.
  • ORDER BY goes only at the end of the entire UNION query.
  • Use UNION for unique reports; use UNION ALL for complete data logs.