Explain Self Join

Question

I have tried to understand self-join studying on the Internet, but couldn't find satisfactory explanation. can anybody please explain it with an example like where to use it and why to use it? would be nice if you can explain it with query.

Answer 1

Wikipedia's Join (SQL) page has a specific entry about self-join, including an example.

As another example, suppose you had a Person table with columns of Id and ParentId to indicate parentage, you might do something like:

SELECT parent.Name, child.Name
FROM Person parent
INNER JOIN Person child ON parent.Id = child.ParentId

Answer 2

Reasons for using a self join me be a hierarchy.

E.g.: You have all employees in a table. Every employee has a manager. So you can link an employee with his manager.

Table emp (loyee). cols emp_id, manager, name

so you could get all employees together with the name of the manager

select e.name employeename, m.name managername 
from emp e, emp m where e.manager = m.emp_id