#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

Self JOIN in MySQL: Use Cases and Examples

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;
employeemanager
Karan MalhotraSunita Rao
Lakshmi NairSunita Rao
Aditya JoshiKaran Malhotra
Pooja VermaKaran Malhotra
Nikhil ReddyLakshmi 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;
employeemanager
Sunita Rao(no manager)
Karan MalhotraSunita Rao
Lakshmi NairSunita Rao
Aditya JoshiKaran Malhotra
Pooja VermaKaran Malhotra
Nikhil ReddyLakshmi 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;
employeesalarymanagermanager_salary
Pooja Verma145000Karan Malhotra140000

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_1colleague_2city
Sunita RaoLakshmi NairBengaluru
Sunita RaoNikhil ReddyBengaluru
Lakshmi NairNikhil ReddyBengaluru
Karan MalhotraAditya JoshiGurugram
Karan MalhotraPooja VermaGurugram
Aditya JoshiPooja VermaGurugram

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_idday_1day_2
72026-11-012026-11-02
82026-11-032026-11-04
82026-11-042026-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;
managerdirect_reports
Sunita Rao2
Karan Malhotra2
Lakshmi Nair1
Aditya Joshi0
Pooja Verma0
Nikhil Reddy0

COUNT(e.emp_id) counts only matched rows, so people with no reports show 0. COUNT(*) would wrongly show 1 for them.

Common mistakes

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.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →