Joining a table with itself in SQL

Hi DQ Community! I’m working on Screen 3 of the Less Common Joins chapter. I arrived at the correct answer but I didn’t fully understand the reason it was correct (instead of a different way of writing the code, which I will list at the end of this post as well.)

Here is the correct answer:
SELECT e1.first_name ||’ ‘|| e1.last_name AS report,
e2.first_name ||’ '|| e2.last_name AS manager
FROM employee AS e1
LEFT JOIN employee AS e2
ON e1.reports_to = e2.employee_id;

And the output it produces:

Here is the wrong answer:
SELECT e1.first_name ||’ ‘|| e1.last_name AS report,
e2.first_name ||’ '|| e2.last_name AS manager
FROM employee AS e1
LEFT JOIN employee AS e2
ON e1.employee_id = e2.reports_to;

And the wrong output this wrong answer produces:

Why does switching the final line from “ON e1.reports_to = e2.employee_id” to “ON e1.employee_id = e2.reports_to” produce a different output?

Thank you! (I tried to keep the text river, but the formatting keeps getting changed once I click submit. Sorry if the code looks confusing because of it. I’m not sure how to stop the spaces/indents from being removed.)

@Nishat

Welcome to the community!

Kindly refer to this:

1 Like