Learn SQL
JOINS - SELF JOIN
38.1
Lesson

SELF JOIN

The table students contains a column studentMentorId. It stores the id of another student who is a student mentor of the student in a particular row.

Let’s say we want to display the name of all students who have a student mentor, along with the name of their student mentors. Let’s name the headers studentName and mentorName respectively.

In this case, the data for both our result columns, belongs to one and the same column in students, i.e. name. So, we need join the table students with itself. This kind of a JOIN is called a SELF JOIN.

We cannot mention the same combination of table name + column name in the SELECT list more than once, so we will use table aliases to achieve that. The query would look like.

Observe that we have used two different aliases s1 and s2 for the same table students, and then we can treat them as two different tables and apply standard JOIN logic to them.