Self JOIN in SQL

Level 4154 keystrokes

A self JOIN joins a table to itself; two aliases let one copy act as the employee and the other as the manager.

SQL code

-- Return the employee's name and their manager's name
1SELECT
2 e.name AS employee,
3 m.name AS manager
4FROM
-- Alias e: the employees table seen as employees
5 employees e
-- Join the same table again as m (the managers), linking manager_id to id
6LEFT JOIN
7 employees m ON e.manager_id = m.id
-- Drop employees with no manager_id (e.g. the boss), so LEFT JOIN keeps nothing extra here
8WHERE
9 e.manager_id IS NOT NULL
-- Sort by manager name
10ORDER BY
11 m.name;

Output

+----------+---------+
| employee | manager |
+----------+---------+
| Bob      | Alice   |
| Carol    | Alice   |
| Dave     | Bob     |
+----------+---------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • .× 6
  • _× 2
  • ,× 1
  • ;× 1
  • =× 1

More SQL examples at level 4

← SQL typing practice