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.
And so on...