SQL - Joining tables to themselves

It's strange what kinds of things you stow away from all those years of school. I remember the day I first learned in biology class that most bacteria reproduce asexually. No couples therapy for them, I guess. No nights in the doghouse or roses on Valentine's Day or any of that nonsense that humans have to deal with just to please their significant others.

My abnormal(?) fascination with this concept probably explains why I immediately thought of bacteria when I got to the lecture on self-joining tables in the Khan Academy SQL course.

This is a rather twisted topic, so I thought I would write out the concepts, just to help myself remember and understand what's going on when you try to join a table to itself.

We'll use the example from the course. We have a table of students' first names, last names, email addresses and buddy_ids. Each student has a different buddy. If we want to generate a table that shows Student 1's first name & last name alongside Student 1's buddy's email, then we would issue the following command:
SELECT students.first_name, students.last_name, buddies.email as buddy_email FROM students JOIN students buddies ON students.buddy_id = buddies.id;

In a normal JOIN command you have two separate tables, but since in this case, you're joining a table to itself, you need to create an alias for the table name. In this case, the alias for students is buddies, indicated by "students buddies" above.

The ON portion is what ties the fields together (what info to join the data on).

"buddies.email as buddy_email" -- the "as buddy_email" is not required, it's just what you want the column name to be in the data you're selecting.