left join table on itself sql

Question

I have a simple sql question here, I see some queries like this:

SELECT 
    users.id, users.username, users.password
FROM
    users AS User
        LEFT JOIN
    users AS User0 ON ...
        LEFT JOIN
    users AS User1 ON ...
        LEFT JOIN
    users AS User2 ON ...
WHERE
    ...
ORDER BY ... DESC

In which, as you can see above, the same table users is being left-joined 3 times. My question is this, why would somebody wanna join a table on itself? What exactly is the purpose of doing something like that?

Please give me the best explanation with clear examples if possible

Thank you

Answer 1

This is normally done when you have a hierarchy.

Say a table of employees where each employee has a manager - the manager is signified with a ManagerId field that is the managers' employee ID.

If you want to see each employee, their manager, the managers' manager etc, self joins would be the way to write the query.