Self Joins – Intro to Relational Databases

Self Joins – Intro to Relational Databases


Earlier in this course, we’ve used
Joins to derive new tables, from two or more existing tables. But there are also cases, where you
want to join a table to it’s self. For instance to find pairs of entries
that have something in common. Doing this in the database is generally
a lot faster than pulling that data in to your python code and
scanning it there. Here is an example. This is a table of students living,
in the residence halls of a college. Student ID is the primary key, and then we have the name of the
residence building and the room number. These are actually some of the names
of dormitories at my college. Now suppose, we want to find out
which students are roommates. In other words they live in the same
building, and have the same room number. Joining the table to
itself is how we do this. This exact query isn’t quite right,
but it’s close. Try it out on the next page,
then see if you can fix it.

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *