Rainbow Deals - hours only!Up to 80% off on all courses and bundles.-Close
1. Introduction
Dictionary database


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:

id name supervisor_id experience
1 John 0 15
2 Peter 3 3
3 Casper 1 2
4 Kate 1 5

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:

  emp.name as employee_name,
  supervisor.name as supervisor_name
FROM employee as emp
JOIN employee as supervisor
  ON emp.supervisor_id = supervisor.id
employee_name supervisor_name
Casper John
Peter Casper
Kate John

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.


Click the Next exercise button to continue.