Self join in SQL

A table can join itself when a row points at another row of the same kind.

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON m.id = e.manager_id
ORDER BY manager, employee;

How it works

1. Two aliases make the same table look like two tables.
2. A left join keeps the rows whose parent is missing.
3. This is how a hierarchy is flattened one level.

Keywords and builtins used here

• AS
• BY
• FROM
• JOIN
• LEFT
• ON
• ORDER
• SELECT

The run, in numbers

Lines

7

Characters to type

140

Tokens

35

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 25 seconds.

Type this snippet

Step 6 of 9 in Joins, step 14 of 22 in Joins & aggregation.

← Previous Next →