Purpose of SELF JOIN

⭐ What Is a Self Join (in Simple Words)?

A self join means joining the same table with itself to compare one row with another row in the same table.

To avoid confusion, we give the table two names (aliases).

Let’s learn with very easy examples 👇

---

✅ Example 1: Employees and Their Managers (Simple Hierarchy)

Table: employees

id	name	manager_id
1	Karthik	3
2	Priya	3
3	Ramesh	NULL

Here:

• Ramesh is the manager of Karthik and Priya.

✔ Self Join Query:

SELECT e.name AS employee,

       m.name AS manager

FROM employees e

LEFT JOIN employees m

      ON e.manager_id = m.id;

✔ Output:

employee	manager
Karthik	Ramesh
Priya	Ramesh
Ramesh	NULL

Why self join?
Because employees and managers are in the same table.

---

✅ Example 2: Finding People Who Live in the Same City

Table: persons

id	name	city
1	Arun	Chennai
2	Bala	Chennai
3	Devi	Bangalore
4	Mani	Chennai

We want to find pairs of people who are from the same city.

✔ Self Join Query:

SELECT p1.name AS person1,

       p2.name AS person2,

       p1.city

FROM persons p1

JOIN persons p2

     ON p1.city = p2.city

    AND p1.id < p2.id;

✔ Output:

person1	person2	city
Arun	Bala	Chennai
Arun	Mani	Chennai
Bala	Mani	Chennai

Why self join?
Because we compare two people in the same table.

---

✅ Example 3: Finding Duplicate Phone Numbers

Table: customers

id	name	phone
1	Alex	9991110001
2	Balu	9991110001
3	Ram	8882220033

We want to find customers who share the same phone number.

✔ Self Join Query:

SELECT c1.name AS customer1,

       c2.name AS customer2,

       c1.phone

FROM customers c1

JOIN customers c2

     ON c1.phone = c2.phone

    AND c1.id < c2.id;

✔ Output:

customer1	customer2	phone
Alex	Balu	9991110001

Why self join?
To compare one customer’s phone number to other customers’ phone numbers.

---

✅ Example 4: Show Products With Higher Price Than Another Product

Table: products

id	product	price
1	Pen	10
2	Pencil	5
3	Marker	20

We want to find all products that are more expensive than another product.

✔ Self Join Query:

SELECT p1.product AS expensive_product,

       p2.product AS cheaper_product

FROM products p1

JOIN products p2

     ON p1.price > p2.price;

✔ Output:

expensive_product	cheaper_product
Pen	Pencil
Marker	Pen
Marker	Pencil

Why self join?
Because we compare prices of rows in the same table.

---

🎯 Summary (In the Easiest Form)

A self join is used when:

Situation	Why Self Join?
Employee–manager	Both stored in same table
Same city people	Compare rows in same table
Duplicate checking	Compare same table with itself
Compare values (price, age, weight)	Compare one row to another