How exactly self join works?-CodePudding

I solved the task instinctively but still dont really understand how self join works in this case. Can you explain what contain e1 and e tables here? what does table we get from self join contain here?

select e.first_name, e.salary, e.id, e.manager_id, e.employee_title 
from employee e
join employee e1 on e1.id = e.manager_id
where e.salary > e1.salary
order by e.manager_id;

CodePudding user response：

The employee table here contains a hierarchical relationship between employees and their managers.

With the self join you get all employees with their direct manager (on e1.id = e.manager_id). The e1 alias could be better renamed to m or mgr or something, to indicate that it contains the matching manager. Doing that the selection immediately is easier to understand.

   SELECT e.first_name, e.salary, e.id, e.manager_id, e.employee_title 
           , mgr.first_name -- added to clarify
    FROM employee e
    JOIN employee mgr on mgr.id = e.manager_id
    WHERE e.salary > mgr.salary
    ORDER BY e.manager_id;

Also, with the current where condition you only select employees that have a bigger salary than their manager.