Understanding How to Write the On Intra-Clause Primary Key During Self-Coupling

Nice to meet you.I have just learned SQL.
I started studying using an overseas practice site, but when I was solving the questions in the link below, I came up with a question.

https://pgexercises.com/questions/joins/self2.html

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

The above is the answer code for the question, but the contents of on are

leftouter join cd.members recs
            on memes.memid=recs.recommendedby

The answer changed when I replaced it as shown in .
Please let me know if you don't mind because I can't explain the cause clearly.

sql postgresql

2022-09-30 19:38

1 Answers

I think it will be easy to understand if you print out the items that are joining conditions.

For Answer Codes

select mems.firstname as memfname, mems.surname as memsname, mems.recommendedby as menby, recs.memid as recid, recs.firstname as recfname, recs.surname as recsname
from cd.members memes
leftouter join cd.members recs on recs.memid = mems.recommendedby
order by memsname, memfname;

If you replace the contents of on

select memes.memid, mems.firstname as memfname, mems.surname as memfname, recs.firstname as recfname, recs.surname as recsname, recs. recommendedby as recby
from cd.members memes
leftouter join cd.members recs on mems.memid=recs.recommendedby
order by memsname, memfname;

In other words, the answer code shows who recommended the member, in short, who recommended the member, and when you replace the on, the member recommended the member.

2022-09-30 19:38

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656