Understanding more on topic "Self Join"

SQL
1

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.

2

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

select 
     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
QueryPlan

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
3

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

4

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:

SELECT NULL = NULL, NULL <> NULL, NULL > NULL, NULL < 1;
5

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