Why do we need to compare keys from different table?
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.