hown position of joined tables influence results?-CodePudding

I have this question to solve:

How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).

which I thought to resolve writing this code

SELECT mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
     from 
         cd.members mems
         left  outer join cd.members recs
            on mems.memid = recs.recommendedby
order by mems.surname, mems.firstname

but the correct answer was:

select mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
    from 
        cd.members mems
        left outer join cd.members recs
            on recs.memid = mems.recommendedby
order by memsname, memfname

I'm a little confused to understand how the order of the same table in this join on the "on", influence the results, also isn't an easy topic to disclosure via google, could someone help me to understand it? Thank you!

CodePudding user response：

It is not a matter of table order, it is a matter of the meaning of your fields. If you have 2 members 1 and 2. You seem to have exported that 1 recommends 2 when you were expected to export that 2 is recommended by 1.

Take this fake example illustrating the above; with only 2 records, it should make the point clearer.

WITH members(memid, recommendedby, description) AS (
VALUES (1, NULL, 'Recommending'), (2, 1, 'Recommended')
)
SELECT * FROM members

And from there, this is an equivalent of what you have done:

WITH members(memid, recommendedby, description) AS (
VALUES (1, NULL, 'Recommending'), (2, 1, 'Recommended')
)
SELECT mems.description AS "member who is recommended",
       recs.description AS "member who is recommending"
FROM members mems
LEFT OUTER JOIN members recs ON mems.memid = recs.recommendedby

But members from mems are supposed to be recommended by members from recs, not the other way around.
You were supposed to do:

WITH members(memid, recommendedby, description) AS (
VALUES (1, NULL, 'Recommending'), (2, 1, 'Recommended')
)
SELECT mems.description AS "member who is recommended",
       recs.description AS "member who is recommending"
FROM members mems
LEFT OUTER JOIN members recs ON recs.memid = mems.recommendedby