SELF JOIN — Complete Guide
SELF JOIN — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of MySQL Tutorial on Toolliyo Academy.
On this page
MySQL Tutorial · Lesson 24 of 100
SELF JOIN
Basics → Advanced
Basics · 1 — SQL · ~6 min · MySQL — Joins & Relationships
What is this?
SELF JOIN joins a table to itself with aliases. Common for hierarchies: employee → manager, category → parent category.
Why should you care?
Org charts and referral trees in fintech apps store parent_id in the same employees or users table.
See it live — copy this example
Run in MySQL Workbench or the mysql CLI.
SELECT e.full_name AS employee,
m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;What happened?
- e is the worker row; m is another row in employees linked by manager_id.
- NULL manager means top-level role.
Practice next
- CREATE employees with manager_id self-FK.
- Insert CEO (NULL manager) and two reports.
- Run SELF JOIN; verify hierarchy.
- Add WHERE m.full_name IS NULL for top bosses only.
- COUNT reports: GROUP BY m.employee_id.
Remember
Same table, two aliases. Models trees with parent_id. LEFT JOIN for optional parent.
DataFlow sales hierarchy
Commission rollup uses SELF JOIN to attach each rep to regional manager.
Outcome: Payroll CSV matches org structure.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
