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
In this case, the data for both our result columns, belongs to one and the same column in
name. So, we need join the table
students with itself. This kind of a
JOIN is called a
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
s2 for the same table
students, and then we can treat them as two different tables and apply standard
JOIN logic to them.