Self Joins

As a data analyst, you will have to combine data from different tables. This is where joins come in. But sometimes, you will have to join tables with themselves. Sound interesting?

Let's talk about self joins.

Self Joins is a special kind of join and like the name insinuates, you join the table with itself, weird huh? It would make sense to join two tables that contain different attributes for the same primary key. Say a table with students' personal information and another one populated with student's fees paid. If you wanted to find out, for instance, how many students have cleared their school fees, you'd join these two tables. Easy!

Self joins are used especially when working with hierarchal tables whereby different columns in the same table have relationships within themselves. For instance, let's say in the table above, your students recommend other students and get a discount per recommendation who joins the school. To get the student along with their referee, we'd have to self join the table with itself on the student id. Assuming the student id is the primary key. This way, we get a list of the students along with those that they referred.

When using self joins it's important to use aliases since it is the same table being joined to itself. We treat each alias like a different table. Your code would look something like this,

SELECT student.id AS student, recommendation.id AS recommender

FROM students AS students

INNER JOIN

students AS recommendation

ON

students.id = recommendation.id

#dataanalysis #sql