Welcome back to our JOINs course! In this part, we'll focus on joining tables with themselves!

Let's consider the following situation: we have information about employees and their supervisors in a single table, like this:

As you can see, there are four people. John is a supervisor to both Casper and Kate, and Casper is Peter's supervisor.

The employee table stores data in a hierarchical structure: employees and their supervisors. Storing a structure like this in a table is quite common. Imagine you want to list each employee's name along with the name of their supervisor. That's where JOINing a table with itself comes in handy:

SELECT
  emp.name as employee_name,
  supervisor.name as supervisor_name
FROM employee as emp
JOIN employee as supervisor
  ON emp.supervisor_id = supervisor.id

When you join a table with itself, you must alias both occurrences of the table name. Moreover, the column names you refer to must be preceded by the alias of the table you want. This way, the database can distinguish which copy of the table you want to select a particular column from.