🔁 SELF JOIN in SQL

Last Updated: 7th January 2026

---

A SELF JOIN is a join where a table is joined with itself. It is used when rows in the same table are related to each other.

• Only one table
• Table is treated as two separate copies
• Uses table aliases to differentiate

Hinglish Tip 🗣: Jab same table ke data ko aapas me compare / relate karna ho → SELF JOIN.

Basic Syntax

SELECT columns
FROM table_name t1
JOIN table_name t2
ON t1.column = t2.column;

⚠️ Aliases (t1, t2) are mandatory, otherwise SQL gets confused.

---

Example: Employee–Manager Relationship

employees

emp_id	emp_name	manager_id
1	Rahul	NULL
2	Anjali	1
3	Aman	1
4	Neha	2

Here:

• manager_id refers to emp_id of same table

---

📄 SELF JOIN Example

SELECT e.emp_name AS employee,
       m.emp_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

✅ Result

employee	manager
Rahul	NULL
Anjali	Rahul
Aman	Rahul
Neha	Anjali

Explanation:

• Table joined with itself
• e → employee
• m → manager

---

Example: Compare Rows in Same Table

Find employees with same salary

SELECT e1.emp_name, e2.emp_name, e1.salary
FROM employees e1
JOIN employees e2
ON e1.salary = e2.salary
AND e1.emp_id <> e2.emp_id;

---