Employees and Managers
A classic hierarchy example.
Employees and Managers is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is a Self Join?
A self join is when a table is joined to itself. This sounds unusual, but it is extremely useful when rows in a table have a relationship with other rows in the same table.
The classic example is an employees table where each employee may have a manager — and that manager is also an employee stored in the same table.
The Employees Table
Let us create a simple employees table. Each employee has an id, a name, and a manager_id that points back to another row in the same table.
When manager_id is NULL, the employee has no manager — they are at the top of the hierarchy (e.g., the CEO).
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id INT REFERENCES employees(id)
);Inserting Sample Data
Let us populate the table with a small company hierarchy. Alice is the CEO (no manager). Bob and Carol both report to Alice. Dave and Eve report to Bob.
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);Querying Without a Join
If we just select all rows, we see the raw manager_id numbers — not the manager names. This is hard to read and not useful to end users.
SELECT id, name, manager_id
FROM employees;Writing the Self Join
To show the manager's name next to each employee, we join the employees table to itself. We give each copy a different alias: e for the employee and m for the manager.
The join condition links the employee's manager_id to the manager's id.
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;Including Employees With No Manager
The INNER JOIN above hides Alice because her manager_id is NULL — there is no matching row on the right side. To include everyone (even the top-level employee), use a LEFT JOIN.
Alice will appear with NULL in the manager column.
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Using COALESCE for Readability
NULL in the manager column can be confusing. We can use COALESCE to replace NULL with a readable label like 'No Manager', making the output cleaner for reports.
SELECT
e.name AS employee,
COALESCE(m.name, 'No Manager') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY manager, employee;Finding Direct Reports
We can flip the query to ask: who reports to a specific manager? This is called finding direct reports. Here we find all employees who report directly to Alice (id = 1).
SELECT e.name AS direct_report
FROM employees e
WHERE e.manager_id = 1;Counting Reports Per Manager
Combining a self join with GROUP BY and COUNT lets us see how many direct reports each manager has. This is useful for workload analysis.
SELECT
m.name AS manager,
COUNT(e.id) AS direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.name
ORDER BY direct_reports DESC;Two Levels Deep
To see two levels of hierarchy at once — employee, their manager, and the manager's manager — we can join the table a third time. Each join adds another level of the hierarchy.
SELECT
e.name AS employee,
m.name AS manager,
gm.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;When Self Joins Have Limits
Self joins work well for fixed, shallow hierarchies (1–3 levels). If the depth is unknown or unlimited — like a company org chart that can go 10 levels deep — you need recursive CTEs instead.
For most practical employee-manager queries, the self join approach covered in this lesson is the right tool.
Quick Check
Test your understanding of self joins and the employees-managers hierarchy.
Lesson Recap
In this lesson you learned how to use a self join to model and query a classic employee-manager hierarchy stored in a single table.
Key takeaways:
- A self join joins a table to itself using two different aliases.
- The join condition links
manager_idtoidin the same table. - Use INNER JOIN to see only employees who have a manager, and LEFT JOIN to include everyone.
- COALESCE can replace NULL manager values with a readable label.
- For unlimited-depth hierarchies, consider recursive CTEs.
Frequently asked questions
Is the “Employees and Managers” lesson free?
Yes — the full text of “Employees and Managers” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Employees and Managers”?
A classic hierarchy example. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Employees and Managers” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- What Is a Self Join
- Employees and Managers
- Comparing Rows in the Same Table
- Limits of Self Joins