What is a SELF JOIN?

A self join is when we join a table with itself. It is useful when a table contains a relationship between rows.

Real-Life Example: Employee & Manager

Imagine a company where every employee has a manager. We store this information in a table called employees.

Step 1: Creating an Employees Table

emp_id	emp_name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	2

• Alice (emp_id = 1) is the boss (No manager, so NULL).

• Bob (emp_id = 2) & Charlie (emp_id = 3) report to Alice (manager_id = 1).

• David (emp_id = 4) & Eve (emp_id = 5) report to Bob (manager_id = 2).

Step 2: Understanding the Query

We need to join the table with itself to find the manager of each employee.

SELECT e1.emp_name AS Employee, e2.emp_name AS Manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.emp_id;

Step 3: How It Works

• e1 is the employee table (The main table).

• e2 is the same employee table but used to find managers (Self join).

• The condition ON e1.manager_id = e2.emp_id means:

  • Find the emp_name from e1 (employee).

  • Find the corresponding emp_name from e2 (manager) where e1.manager_id = e2.emp_id.

Step 4: Output

Employee	Manager
Bob	Alice
Charlie	Alice
David	Bob
Eve	Bob
Alice	NULL

• Bob and Charlie's manager is Alice.

• David and Eve's manager is Bob.

• Alice has no manager (NULL).

Final Summary

A self join is useful for hierarchical data like employees and managers. We join the same table twice, one for employees and one for managers.