Not Quite Understanding The Query After Just Shifting Column Names

This is my table tblEmployee ,when I'm trying to use a self join query it's result is varying while shifting columns, can someone please explain me the difference between those two

Solution 1:

First a simple explanation: the left join takes every row in the first table that has at least a match in the second table on the column you selected.

In the first query you are considering the Manager ID on every row and looking for a match on the Employee ID in the second table: since value in the second table (Employee ID) is a key (at least in the example definition), you can find only one row that matches that ID.

Eg: First row Mike has Manager ID = 3, when you are looking at the table you have only one row that has Employee ID = 3, and that is Todd

In the second query you are considering the Employee ID on every row and looking for a match on the Manager ID in the second table: since there could be more than one row with the same Manager ID, you will receive more value for every row.

Eg: First row Mike has Employee ID = 1, when you are looking at the table you have three rows with anager ID = 1 and those are Rob, Ben and Sam.

Solution 2:

With Query1 you are selecting each employee (E) and their manager (M) if there is one (left join).

With Query2 you reversed that. You are selecting each employee (E) and their subordinates (M). The alias for M.Name is incorrect now, because it's not the manager but a subordinate. As some employees have more than one subordinate, they are listed once for each of them (Mike).

Solution 3:

Basically the second query is little out of order in the sense that since you have swapped the join criteria columns, you get

all combination of manager name followed by the employee name they are manager of

unlike the first query which gave you

all combination of employee names followed by their manager name

So your corrected 2 query should be

SELECT E.Name AS Manager, M.Name AS Employee -- note changes here
from tblEmployee E
LEFT JOIN tblEmployee M
on E.EmployeeID = M.ManagerID  

OUTPUT

ManagerEmployee

Mike       Rob
Mike       Ben
Mike       Sam
Rob        NULL
Todd       Mike
Ben        NULL
Sam        NULL

Or maybe you wanted the same result the other way round

then your query should be using RIGHT JOIN instead of LEFT JOIN

SELECT E.Name AS Employee, M.Name AS Manager
from tblEmployee E
RIGHT JOIN tblEmployee M -- note the change inJOINon E.EmployeeID = M.ManagerID  

OUTPUT

EmployeeManager

 Todd        Mike   
 Mike        Rob    
 NULL        Todd   
 Mike        Ben    
 Mike        Sam