MySQL Self Join
MySQL Self Join – Complete Guide with Examples
A Self Join in MySQL is a join where a table is joined with itself.
It’s useful when rows in the same table are related to each other.
Even though it’s the same table, we treat it as two different tables using aliases.
What Is a Self Join?
A Self Join:
Uses the same table twice
Requires table aliases
Compares rows within the same table
Syntax (Basic)
aandbare aliases- Table is logically split into two copies
Why Do We Need Self Join?
Self Join is commonly used when:
Data has hierarchical relationships
One row refers to another row in the same table
Comparing rows within the same table
Common Examples
Employee & Manager
Student & Mentor
Product price comparison
Finding duplicate records
Example Table: employees
| emp_id | emp_name | manager_id |
|---|---|---|
| 1 | Amit | NULL |
| 2 | Rahul | 1 |
| 3 | Neha | 1 |
| 4 | Priya | 2 |
Example 1: Employee and Manager (Most Common)
Query
Output
| employee | manager |
|---|---|
| Amit | NULL |
| Rahul | Amit |
| Neha | Amit |
| Priya | Rahul |
- Same table used twice
LEFT JOINkeeps top-level managers
Example 2: Find Pairs from Same Table
Table: students
| id | name | marks |
|---|---|---|
| 1 | A | 85 |
| 2 | B | 90 |
| 3 | C | 85 |
Find students with same marks
- Avoids duplicate pairs
- Uses comparison inside same table
Example 3: Compare Rows in Same Table
Find employees earning more than others
- Row-to-row comparison
- Powerful analytical use
Self Join with WHERE Clause
- Old-style syntax (works but JOIN is preferred)
INNER JOIN vs LEFT JOIN in Self Join
| Join Type | Behavior |
|---|---|
| INNER JOIN | Only matching rows |
| LEFT JOIN | Keeps all left table rows |
| RIGHT JOIN | Rarely used |
| SELF JOIN | Can use any join type |
Important Rules for Self Join
- Always use table aliases
- Use clear join conditions
- Avoid infinite matches
- Use
id < idor<>to prevent duplicates
Common Mistakes
- Forgetting aliases
- Joining without condition (Cartesian join)
- Duplicate row results
- Using WHERE instead of JOIN unnecessarily
- Confusing SELF JOIN with SUBQUERY
Self Join vs Subquery
| Self Join | Subquery |
|---|---|
| Faster in many cases | Can be slower |
| More readable | Sometimes simpler |
| Better for comparisons | Better for aggregates |
Interview Questions: MySQL Self Join
1. What is a self join?
Joining a table with itself.
2. Why are aliases needed?
To treat same table as different references.
3. Is SELF JOIN a keyword?
No, it’s a concept.
4. Can self join use LEFT JOIN?
Yes.
5. Common real-world example?
Employee–Manager relationship.
Summary
- Self Join joins a table with itself
- Uses table aliases
- Useful for hierarchical & comparison data
- Powerful for real-world queries
- Frequently asked in interviews
Mastering MySQL Self Join will greatly improve your SQL logic, data analysis skills, and interview confidence
