Explain how the request works?

Question

SQL

It is proposed to build a query
Display a list of employees who receive wages greater than those of their immediate supervisor.
Answer

select a.*
from   employee a, employee b
where  b.id = a.chief_id  and  a.salary > b.salary

The where b.id = a.chief_id part of the query is not clear. Tell me why exactly

Answer 1

1. We connect one table twice, the database works as if with different tables (a and b).
we consider table a as employees
table b as bosses, why is this:
2. We make a connection between records through b.id = a.chief_id, it says the employee (a) has a boss under chief_id, if chief_id is not specified, then this is the boss, which means he is not included in the sample
3 A condition a.salary > b.salaryis added that the salary of the employee (a) is greater than the salary of his boss (b).
4. If the conditions (where) are satisfied, then select a.*- we display all the data of the employee

Answer 2

Table A is taken entirely from employee , and for table B, only those rows in which id = chief_id of table A are taken from employee. In other words, for each employee you add a line where there is information about his boss.
As a result, you, as it were, work with a composite table (consisting of A and B, see picture). With such a table in which, in each row there is both the salary of the employee and the salary of his manager.
Only in this case you will be able to select rows by the condition a.salary > b.salary .