UNION and UNION ALL in MySQL

UNION Combines Rows; JOIN Combines Columns

UNION is a set operation that stacks compatible query results vertically. JOIN relates tables horizontally and can return columns from both sides in the same row.

Verified Club Dataset

CREATE TABLE club_a (
  student_name VARCHAR(60) PRIMARY KEY
);
CREATE TABLE club_b (
  student_name VARCHAR(60) PRIMARY KEY
);

INSERT INTO club_a VALUES
('Aarav'), ('Meera'), ('Sana');

INSERT INTO club_b VALUES
('Aarav'), ('Kabir'), ('Sana');

Each club has three members. Aarav and Sana occur in both query results. Across both branches there are six occurrences but only four distinct names.

UNION Removes Duplicate Result Rows

SELECT student_name FROM club_a
UNION
SELECT student_name FROM club_b
ORDER BY student_name;

Duplicate elimination examines the projected result row. If another column such as source label differs, the rows are not duplicates even when student_name is the same.

UNION ALL Preserves All Occurrences

SELECT student_name FROM club_a
UNION ALL
SELECT student_name FROM club_b
ORDER BY student_name;

Six input occurrences produce six output rows. This is correct for attendance events, transactions or source-preserving pipelines where repeated values represent separate facts.

Column Count, Position and Type Rules

SELECT student_name AS person, 'Club A' AS source
FROM club_a
UNION ALL
SELECT student_name, 'Club B'
FROM club_b;

Both branches return two columns. Corresponding positions must be type-compatible; names come from the first branch, so the final columns are person and source. Do not rely on implicit numeric-to-text conversions when an explicit CAST makes the intended result type clear.

Global ORDER BY and Branch LIMIT

SELECT student_name FROM club_a
UNION ALL
SELECT student_name FROM club_b
ORDER BY student_name;

The final ORDER BY applies to all six rows. When a branch must select its own top rows, parenthesize that query block:

(SELECT student_name
 FROM club_a
 ORDER BY student_name
 LIMIT 2)
UNION ALL
(SELECT student_name
 FROM club_b
 ORDER BY student_name
 LIMIT 2)
ORDER BY student_name;

Without final ORDER BY, display order is not guaranteed even if an individual branch has an access order.

Preserve Source and Count Memberships

SELECT student_name, 'A' AS club_code
FROM club_a
UNION ALL
SELECT student_name, 'B'
FROM club_b
ORDER BY student_name, club_code;

To count club memberships, wrap this result in a derived table and GROUP BY student_name. Aarav and Sana count 2; Kabir and Meera count 1.

Performance, Mistakes and Practice

• Choose duplicate semantics before choosing syntax.
• Project only required columns; wider rows make distinct processing heavier.
• Filter inside each branch when logically safe.
• Use compatible collations and data types.
• Apply final ORDER BY once and use EXPLAIN for representative queries.

Practice: return a distinct four-name roster; retain six membership events; add source labels and explain why UNION no longer collapses shared names; calculate member counts from UNION ALL.

Official References

• MySQL 8.4: UNION Clause
• MySQL 8.4: Set Operations
• MySQL 8.4: SELECT Statement

Syntax and behavior were checked against the official MySQL 8.4 manual. Test every query on a disposable copy before production use.