Understanding more on topic "Self Join"

In the self join turotial, i understand the code, but do not undrestand how sql is doing what doing it and unable to graps the concept how thing work in background, so that i can use it any other scenario other than matching who reports to with employees_id.
i.e. i understand what code is doing - but how its doing i am unable to understand that, which implies i can write same code when given exact same problem but cannot use it anywhere else if some other data comes - because i am not able to understand whats happening in the background.

Given the course data and the query from the lesson (added some aliases for clarity)

     employee.employee_id, employee.first_name employee_name,
     manager.first_name manager_name    
from employees employee
join employees manager
     on employee.reports_to = manager.employee_id;

what happens in the background is this

So we read every row in the employees table (“full table scan”). For each row (employee) we take the value from the reports_to column of the employee to look up the manager for the current employee. The manager is the employee identified by that value.

1 Like

Thanks a million - i am new to programming language - so such prompt response, really encouraged to faster steps in learning SQL.
Okay, please either confirm or correct my understanding that - ON condition is making SQL to run every row from report_to(value) column to look for same value in employee_id column and the produce the result. The follow up to this question is - when we run this query - why the NULL result dosen’t show up against the row in which manager’s employee id is mentioned.
(i can get that result by using left join syntax) , however why sql is eliminating the row from employee_id who is also manager is mentioned. so this elimination is inherernt to sql or i am still missing something basic to graps the concept

Yes. A self join is just a join that joins a table with itself. There’s nothing really special about it.

An inner join combines rows that have matching values in the join columns. There’s no employee_id NULL so there’s no match so rows with NULL in one of the join columns are not included in the result set. And BTW: A comparison with NULL is neither true nor false:


thanks a million for the response and investing time for my doubt.