Who is Aditya's manager? In most HR databases the answer sits in the same table as Aditya: every employee row has a manager_id that points to another row of employees. To put an employee's name next to the manager's name, you join the table to itself. That is all a self join is: an ordinary JOIN where both sides are the same table, told apart by two different aliases. MySQL has no SELF JOIN keyword.
Sample table
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(30),
city VARCHAR(20),
salary INT, -- monthly, in rupees
manager_id INT NULL,
FOREIGN KEY (manager_id) REFERENCES employees (emp_id)
);
INSERT INTO employees VALUES
(1,'Sunita Rao','Bengaluru',250000,NULL),
(2,'Karan Malhotra','Gurugram',140000,1),
(3,'Lakshmi Nair','Bengaluru',150000,1),
(4,'Aditya Joshi','Gurugram',90000,2),
(5,'Pooja Verma','Gurugram',145000,2),
(6,'Nikhil Reddy','Bengaluru',85000,3);
Sunita is at the top and has no manager, so her manager_id is NULL.
Use case 1: employee and manager side by side
Read the aliases as two copies of the table: e plays the employee, m plays the manager. The join condition connects the employee's manager_id to the manager's emp_id.
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;
| employee | manager |
|---|---|
| Karan Malhotra | Sunita Rao |
| Lakshmi Nair | Sunita Rao |
| Aditya Joshi | Karan Malhotra |
| Pooja Verma | Karan Malhotra |
| Nikhil Reddy | Lakshmi Nair |
Only five rows: Sunita disappeared because an inner join drops rows with no match, and NULL never equals anything. To keep her, use a LEFT JOIN:
SELECT e.name AS employee, IFNULL(m.name, '(no manager)') AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;
| employee | manager |
|---|---|
| Sunita Rao | (no manager) |
| Karan Malhotra | Sunita Rao |
| Lakshmi Nair | Sunita Rao |
| Aditya Joshi | Karan Malhotra |
| Pooja Verma | Karan Malhotra |
| Nikhil Reddy | Lakshmi Nair |
Use case 2: compare a row with its related row
Once both rows are on one line, you can compare their columns. Which employees earn more than their own manager?
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;
| employee | salary | manager | manager_salary |
|---|---|---|---|
| Pooja Verma | 145000 | Karan Malhotra | 140000 |
This exact question is a long-standing SQL interview favourite, and the self join is the expected answer.
Use case 3: pairs from the same group
List every pair of colleagues who work in the same city, for example to plan car-pools:
SELECT a.name AS colleague_1, b.name AS colleague_2, a.city
FROM employees a
JOIN employees b
ON a.city = b.city
AND a.emp_id < b.emp_id
ORDER BY a.city, a.emp_id, b.emp_id;
| colleague_1 | colleague_2 | city |
|---|---|---|
| Sunita Rao | Lakshmi Nair | Bengaluru |
| Sunita Rao | Nikhil Reddy | Bengaluru |
| Lakshmi Nair | Nikhil Reddy | Bengaluru |
| Karan Malhotra | Aditya Joshi | Gurugram |
| Karan Malhotra | Pooja Verma | Gurugram |
| Aditya Joshi | Pooja Verma | Gurugram |
The condition a.emp_id < b.emp_id does two jobs: it stops a person pairing with themselves, and it keeps only one of (Sunita, Lakshmi) and (Lakshmi, Sunita). Without it, joining on city alone returns 18 rows: each of the six people matched with all three people in their city, including themselves.
Use case 4: compare a row with the "next" row
Self joins also find consecutive events. Which users logged in on two days in a row?
CREATE TABLE daily_logins (
user_id INT,
login_date DATE,
PRIMARY KEY (user_id, login_date)
);
INSERT INTO daily_logins VALUES
(7,'2026-11-01'),(7,'2026-11-02'),(7,'2026-11-04'),
(8,'2026-11-03'),(8,'2026-11-04'),(8,'2026-11-05');
SELECT d1.user_id, d1.login_date AS day_1, d2.login_date AS day_2
FROM daily_logins d1
JOIN daily_logins d2
ON d2.user_id = d1.user_id
AND d2.login_date = d1.login_date + INTERVAL 1 DAY
ORDER BY d1.user_id, d1.login_date;
| user_id | day_1 | day_2 |
|---|---|---|
| 7 | 2026-11-01 | 2026-11-02 |
| 8 | 2026-11-03 | 2026-11-04 |
| 8 | 2026-11-04 | 2026-11-05 |
In MySQL 8 the LAG() and LEAD() window functions can answer "compare with the previous row" questions without a join, and are usually simpler when the rows are not exactly one day apart. See the window functions tutorial.
Bonus: counting direct reports
SELECT m.name AS manager, COUNT(e.emp_id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.name
ORDER BY direct_reports DESC, m.emp_id;
| manager | direct_reports |
|---|---|
| Sunita Rao | 2 |
| Karan Malhotra | 2 |
| Lakshmi Nair | 1 |
| Aditya Joshi | 0 |
| Pooja Verma | 0 |
| Nikhil Reddy | 0 |
COUNT(e.emp_id) counts only matched rows, so people with no reports show 0. COUNT(*) would wrongly show 1 for them.
Common mistakes
- No aliases.
FROM employees JOIN employeesfails with error 1066 "Not unique table/alias". Each copy needs its own alias. - Join condition the wrong way round.
ON e.emp_id = m.manager_idgives you each person's reports, not their manager. Say the sentence out loud: "the manager's id equals the employee's manager_id". - Inner join when the top of the tree matters. Use
LEFT JOINto keep rows whose parent isNULL. - Expecting the whole chain. One self join climbs one level. For a full chain (employee, manager, manager's manager, and so on) use a recursive CTE.
- Pair queries that explode. Joining a large table to itself on a low-variety column (city, status) can produce millions of rows. Filter first and always add the
a.id < b.idcondition for pairs.
To see how self joins relate to the other join types, read MySQL JOINs explained; the "every combination" join is covered in CROSS JOIN in MySQL.