SQL self join: Why INNER JOIN hides an employee without a manager

You query four employees and their managers, but the result contains only three people. The missing person is Mira, who has no manager. Her data has not disappeared. The question is how the table is joined to itself and which side's rows survive.

How do employees and managers connect in one table?

Store each employee once in staff. The manager_id column points to another employee's id. Mira has no manager; Jin and Sol report to Mira, and Hana reports to Jin.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INTEGER REFERENCES staff(id)
);

INSERT INTO staff (id, name, manager_id) VALUES
  (1, 'Mira', NULL),
  (2, 'Jin', 1),
  (3, 'Sol', 1),
  (4, 'Hana', 2);

Because manager_id points back to staff, name the table twice in the query: e for employee and m for manager. Join them on e.manager_id = m.id. This does not copy the stored data; aliases distinguish two roles for the same table.

Why does INNER JOIN omit Mira?

First use a join that keeps only rows with a match on both sides. Plain JOIN here means INNER JOIN.

SELECT e.id, e.name AS employee, m.name AS manager
FROM staff AS e
JOIN staff AS m ON e.manager_id = m.id
ORDER BY e.id;

id  employee  manager
2   Jin       Mira
3   Sol       Mira
4   Hana      Jin

Mira's manager_id is NULL, so there is no manager row on the right to match. An inner join excludes her from this result, not from the underlying employee table.

How does LEFT JOIN keep all four employees?

Keep the employee side on the left. A missing manager then leaves the employee row in place and gives it a NULL manager name.

The inner join returns only the three employees with matching managers. The left join retains Mira too, returning four rows.

SELECT e.id, e.name AS employee, m.name AS manager
FROM staff AS e
LEFT JOIN staff AS m ON e.manager_id = m.id
ORDER BY e.id;

id  employee  manager
1   Mira      NULL
2   Jin       Mira
3   Sol       Mira
4   Hana      Jin

For “employees who have managers,” the three inner-join rows are correct. For “all employees and their managers when present,” the four left-join rows are correct. The choice depends on which rows the question must preserve.

What else should you check for a missing manager?

In this example, manager_id IS NULL means there is no manager. In real data it might also indicate missing input; do not infer that every such employee is the organization's head. Check the business rule. An invalid manager ID can likewise produce a NULL manager name after a left join, so referential integrity matters.

Adding WHERE m.name = 'Mira' to the left-join query would leave only Jin and Sol in this dataset. Mira's own manager name is NULL and fails that condition. A LEFT JOIN does not guarantee that all left rows survive later filters. Read ON and WHERE together when the final row count matters.

Key takeaways

A self join uses one table in two roles, employee and manager. An inner join keeps only employees with a matching manager, while a left join retains all four employees including Mira. When the result is too small, inspect the join condition and which rows the query is supposed to preserve.