Concern Regarding Self-Joins

Question

I am learning to program with SQL and have a concern about why the following self-joins behave the way they do.

The table I am joining to itself is the EMPLOYEES table:

I am attempting to match each employee's last_name and employee_id to their respective manager's last_name and employee_id.

The following two queries accomplish this task, but with drastically different output:

select w.last_name, w.employee_id, m.last_name as manager, m.employee_id as manager_id
from employees w join employees m
on (w.manager_id = m.employee_id);

Returns:

select w.last_name, w.employee_id, m.last_name as manager, m.employee_id as manager_id
from employees w join employees m
on (w.employee_id = m.manager_id);

Returns:

From my understanding, these two queries should have identical results because (w.manager_id = m.employee_id) is logically equivalent to (w.employee_id = m.manager_id)

So, why are the results so drastically different?

Answer 1

"these two queries should have identical results because (w.manager_id = m.employee_id) is logically equivalent to (w.employee_id = m.manager_id)"

Up to a point. The logical table referenced by manager_id is the set of the managed; the logical table referenced by employee_id is the set of the managers.

In your first query logical table M is the set of the managers and in the second query it is the set of the managed. This wouldn't matter except that you didn't flip the column aliases when you flipped the join. So m.employee_id as manager_id is correct in the first query but wrong in the second. It's the labels which are different not the data.

This would be clearer it you had sorted the result sets. Add order by m.employee_id, w.manager_id to the first query and order by w.employee_id, m.manager_id to the second query, and you'll find it much easier to reconcile the two outputs.

Answer 2

After a fashion the two queries are returning the same results. They view the same relationship from opposite directions.

In one case the manager appears in the left-most columns and in the other in the right-most columns. Same pairs, different presentation.