Learn SQL  /  Joins  /  Self Join & UNION

Self Join & UNION

A self join compares rows within the same table (useful for hierarchies like manager/employee), and UNION stacks the results of two separate queries into one result set.

Self Join — Comparing Rows Within the Same Table

Add a manager_id column to employees, referring back to another employee\'s emp_id, then join the table to itself using two different aliases.

R Code
-- employees: emp_id | name  | manager_id
--           1      | Rafi  | NULL      (top manager)
--           2      | Meera | 1
--           3      | Zayn  | 1

SELECT staff.name AS employee, manager.name AS manager
FROM employees staff
LEFT JOIN employees manager
  ON staff.manager_id = manager.emp_id;
Output
employee | manager
Rafi     | NULL
Meera    | Rafi
Zayn     | Rafi

Basic UNION

UNION stacks the result rows of two queries on top of each other, removing duplicate rows automatically.

R Code
SELECT name FROM students WHERE class = "A"
UNION
SELECT name FROM students WHERE score > 70;
Output
name
Rafi
Meera
Zayn

UNION vs UNION ALL

R Code
-- UNION removes duplicate rows (slower - has to check)
SELECT class FROM students
UNION
SELECT class FROM students;

-- UNION ALL keeps every row, including duplicates (faster)
SELECT class FROM students
UNION ALL
SELECT class FROM students;
Output
class
A
B

class
A
A
B
B
A
A
B
B
⚠ Common Mistake
Both queries in a UNION must select the same NUMBER of columns, in a compatible order and type - mismatched columns produce an error, and mismatched TYPES in the same position can produce silently wrong results.

Advanced: Self Join to Find Duplicate Rows

🚀 Advanced Example
A self join comparing a table to itself on matching values (but different IDs) is a classic way to detect duplicate records.
R Code
SELECT a.emp_id, b.emp_id, a.name
FROM employees a
JOIN employees b
  ON a.name = b.name AND a.emp_id < b.emp_id;

Key Points

  • A self join joins a table to itself using two different aliases - essential for hierarchical data like manager/employee.
  • UNION combines the results of two queries and removes duplicate rows automatically.
  • UNION ALL keeps all rows including duplicates, and is faster since it skips the deduplication step.
  • Both sides of a UNION must return the same number of columns in compatible types.