Introduction
1. Introduction
Dictionary database
Summary

Instruction

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:

employee
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:

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
RESULT:
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.

Exercise

Click the Next exercise button to continue.

Console

Code editor

Result

TableConsole