Join a table to itself
Relate rows of one table to other rows of the same table with aliases. About 9 minutes.
A column that points at its own table
In a company's employees table, each row has a manager_id. A manager is just another employee, so the id points at a row of the same table.
To show each employee next to their manager's name, you need the table twice: once for the employee, once for the manager. The same table appears twice in the query, under two aliases.
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;
| id | name | manager_id |
|---|---|---|
| 1 | Meera | NULL |
| 2 | Ravi | 1 |
| 3 | Asha | 1 |
| 4 | Kabir | 2 |
| id | name | manager_id |
|---|---|---|
| 1 | Meera | NULL |
| 2 | Ravi | 1 |
| 3 | Asha | 1 |
| 4 | Kabir | 2 |
Kabir's manager_id is 2. Look for the employee with id 2 in the other copy.
Pairs, and who has no partner
The person at the top has no manager (manager_id is NULL), so an inner join would drop them. LEFT JOIN keeps them with a NULL manager.
Self-joins also find pairs: two people in the same city. Joining on city alone pairs a person with themselves, and every pair twice, once each way. Add a.id < b.id to keep each pair once.
SELECT a.name, b.name
FROM members a
JOIN members b ON b.city = a.city
AND a.id < b.id;Why does a self-join need two aliases?
- AThe same table appears twice, so aliases say which copy a column comes from
- BAliases make the join faster
- CAliases rename the columns in the answer
Show the answer
The same table appears twice, so aliases say which copy a column comes from. Without e and m, a column like name could be either copy.
Who reports to whom
The employees table has one row per employee and the id of their manager, which is NULL for the head of the company. Show each employee's name and their manager's name as manager, with NULL for the head. Order by the employee's name.
employees(id, name, manager_id)
Up to 1,000 employees · names are unique