Why do we need to compare keys from different table? can you please help me make sense of this code?

The question: is to find the pair of students who have registered for the same course in the same semester.


why do I need to check the s1.sid = r1.sid ?
Why can't I just the SID in registration table? I know this gives me duplicates, but I don't understand the reason behind.


My understanding behind querying tables to get results is relatively weak, please pardon me if I am asking a fundamental questions :) thanks for helping!

    select s1.sname, s2.sname, cname, r1.semester
    from student s1, student s2, registration r1, registration r2, course c
    where s1.sid = r1.sid and s2.sid = r2.sid 
    and r1.cid = r2.cid and r1.semester = r2.semester 
    and r1.cid = c.cid and s1.sid &lt; s2.sid;
What is the student and registration? Are these tables? I used to plural 's': student_s_, table_s_, to mean a set of objects, which the tables actually are. As of not using s.id = r.sid, it gives you two records (objects), where one refers the other. I guess that r.sid is a foreign key, referencing a student. You then check if two registrations are for the same course at the same semester, then you select that course. By s1.sid &lt; s2.sid, you exclude the same student (which obviously always takes courses with himself) and also exclude duplicates. The duplicates appear because select s1 from students and select s2 from studentds gives you all pairs of students, which means that both ('Joe Bull', 'Sarah The Terrible') and ('Sarah The Terrible', 'Joe Bull') show up. Why duplication in this case?

As of why to check s1.sid = r1.sid look at the CROSS JOIN (aka Cartesian product) filtered with WHERE clause and INNER JOIN. It turns out to be the same thing.

I also believed that there is a computational difference between taking Cartesian product and then filtering it with where but it turns out that modern SQL servers realize that it is just and a join operation and accomplish it in linear N+M time rather than N*M time.