[sql] What is SELF JOIN and when would you use it?

Well, one classic example is where you wanted to get a list of employees and their immediate managers:

select e.employee as employee, b.employee as boss
from emptable e, emptable b
where e.manager_id = b.empolyee_id
order by 1

It's basically used where there is any relationship between rows stored in the same table.

  • employees.
  • multi-level marketing.
  • machine parts.

And so on...