working of the left join

Question

I have an example where in a table there is ID,NAme and M_if(managerID). I populated the table in the following manner

Id  Name M_id
1   A     2
2   B    NUll
3   C     1
4   D     3
5   E     2

Id is employee ID, Name and M_id is manager ID. In above example A's manager is 2(B), B doesn't have manager, C's manager is 1(A) and so on. I need to find out the names of the employees and their managers name. I have written the following query by doing permutations and combinations which gives me proper result but I am not able to comprehend how exactly the query(left join) is working. Please make me explain the concept.

SELECT (e.Name), ee.name FROM test.employee e
left join test.employee ee on ee.Id = e.M_id
order by e.Id;

result i get

A   B
B   
C   A
D   C
E   B

Please explain me the joint

@Prashant, : Please explain me how the left join is working. — user2998990, Feb 23 '15 at 10:04
DISTINCT can be removed... Besides, DISTINCT works on the whole selected rows, it's not a function on a column! "select distinct (a), b" eq. "select distinct a, b" eq. "select distinct a, (b)" — jarlh, Feb 23 '15 at 10:05
possible duplicate of [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) — jpw, Feb 23 '15 at 10:07
@Prashant, self join? This is a self join (same table twice). Left outer join is to also get employees without managers! — jarlh, Feb 23 '15 at 10:07
Although your question perhaps isn't a perfect duplicate I believe you'll find the answer you're looking for in the post I linked. — jpw, Feb 23 '15 at 10:07
@jarlh : i got your point and i know he is using same table for left join but in this case its fine. — Prashant, Feb 23 '15 at 10:11

score 0 · Answer 1 · answered Feb 23 '15 at 10:50

two instances are there for same table as :

e
Id  Name  M_id
1   A     2
2   B    NUll
3   C     1
4   D     3
5   E     2

ee
Id  Name  M_id
1   A     2
2   B    NUll
3   C     1
4   D     3
5   E     2

according to your join condition on ee.Id = e.M_id

simply first row of instance e will be selected because of left join and e.M_id will get compared to ee.Id and 2nd row will be selected from second instance of same table.

selection of data from both the table is as :

e.Id e.Name e.M_id | ee.Id ee.Name ee.M_id 1 A 2 | 2 B NUll 2 B NUll | 3 C 1 | 1 A 2 4 D 3 | 3 C 1 5 E 2 | 2 B NUll

that is why it is showing

A B

working of the left join

1 Answers1