Self JOIN
- SQL course
- Intermediate
- Lesson 4/115
- 4 min
Join a table to itself.
Self JOIN
Joining a table with itself is a self JOIN. You use the same table under two different aliases.
SELECT e.name, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;This query finds each employee's manager.
e.name, m.name AS manager is selected from employees e. employees m is joined on e.manager_id = m.id, keeping only matching rows.
Self JOIN — details
Aliases are mandatory in a self JOIN; otherwise the database cannot tell the two roles apart.
For example, employees e JOIN employees m ON e.manager_id = m.id puts each employee's manager name next to them.
Practice Self JOIN
Join employees with itself to find managers.
Starter code
-- self join
SELECT e.name, m.name AS manager
FROM employees eSample solution
SELECT e.name, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;Expected output
name manager Ayşe Fatma Mehmet Fatma
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add the self join
Question
SELECT e.name, m.name AS manager
FROM employees e
____ employees m ON e.manager_id = m.id;
Answer
SELECT e.name, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;Explanation: INNER JOIN is the right answer.
2 What is mandatory in a self JOIN?
- WHERE
- GROUP BY
- Table aliases Correct answer
- DISTINCT
Explanation: Aliases are needed to distinguish the same table.
3 Self JOIN: match the terms
| Term | Meaning |
|---|---|
self JOIN |
with itself |
alias |
distinguish |
ON |
condition |
hierarchy |
manager |