SQL self join: employees, managers and unique pairs

A self join uses the same table on both sides of a join. One table stores employees, while two aliases let its rows play different roles: employee and manager. There is no separate SELF JOIN keyword to type. You choose an ordinary INNER JOIN or LEFT JOIN and specify how the rows should match.

This reference moves from employee-manager relationships to pairs of people in the same team. The key question is whether the result must retain someone without a match. Your answer determines the join type and where you place a filter. Every query and result table below was checked in SQLite 3.53.4 through Node 24. The data is synthetic.

One table, two roles

Start with a new, empty database and execute this block:

-- example: fixture
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER,
    team TEXT NOT NULL
);
INSERT INTO employees VALUES
    (1, 'Ada', NULL, 'core'),
    (2, 'Ben', 1, 'ops'),
    (3, 'Cleo', 1, 'ops'),
    (4, 'Dan', 2, 'ops'),
    (5, 'Eva', 99, 'qa');

The id column uniquely identifies a person. The manager_id column points to the ID of that person’s direct manager. Ada has no manager in this data. Eva points to 99, which does not exist in the id column. I deliberately include this invalid reference to distinguish an absent manager from a missing referenced record.

For this reason, the exercise does not declare a foreign key on manager_id. In a real model, consider a foreign key and its enforcement to prevent references to nonexistent IDs, according to your system’s rules. A valid reference alone does not rule out cycles in a hierarchy or a person pointing to themselves.

The alias e means employee and m means manager. They name two uses of the table within one query. We do not create a copy of the data or another permanent table. Qualifying columns as e.id and m.id identifies the role being used and resolves ambiguous column names.

LEFT JOIN keeps people without a match

I want to see every employee and, when a match exists, the manager’s name:

-- example: left
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

The result contains five rows. Ada stays in the list with NULL on the manager side. Eva stays too, although her missing match has a different cause. Dan’s row shows Ben because 2 in e.manager_id matches 2 in m.id.

NULL in a result table represents a missing SQL value, rather than the four-letter text string. An equality condition does not match NULL to NULL. When a LEFT JOIN has no match, columns from the right side receive NULL values, as described in the SQLite join documentation.

Each employee appears exactly once here because the right side is matched on the unique key m.id. Joining by name would not provide that guarantee: two people with the same name could multiply the result rows. Before applying DISTINCT to hide that effect, check the join key and the intended relationship.

INNER JOIN returns matched relationships

If I only want employees whose manager has an existing record, I change the join type:

-- example: inner
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
INNER JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

Three rows remain. Ada and Eva disappear from the result because neither has a matching m.id. This does not mean that their records have been removed from the table. Only the conditions for appearing in the report have changed.

A data-quality report often needs those unmatched cases, making LEFT JOIN the more useful choice. A report restricted to existing employee-manager relationships may correctly use INNER JOIN. Define the population you need before choosing the operator.

Separate an absent manager from an invalid reference

Displaying m.name alone does not explain why NULL appeared. I therefore inspect the reference on the left and the matched key on the right:

-- example: classification
SELECT e.id, e.manager_id,
       CASE
           WHEN e.manager_id IS NULL THEN 'no_manager'
           WHEN m.id IS NULL THEN 'missing_reference'
           ELSE 'matched'
       END AS link_status
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

The status no_manager means the employee has no manager reference. The status missing_reference means a reference exists but cannot be found. These are technical categories for the exercise. They do not decide whether having no manager is allowed for a particular role.

I test m.id IS NULL because a matched manager’s primary key cannot be NULL. In a model allowing missing names, testing m.name IS NULL would mix two situations: an unmatched employee and an employee correctly matched to a manager without a name. Choosing the right control column avoids that ambiguity.

Filtering in ON and WHERE produces different results

Suppose I want every employee, but only want to display the manager’s name when that manager belongs to the core team. I put the manager condition in ON:

-- example: on-filter
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id AND m.team = 'core'
ORDER BY e.id;

Dan remains in the list, but his manager Ben belongs to ops and fails the matching condition. The right-hand columns receive NULL. This condition examines the manager’s team through m.team, not Dan’s team.

Now I move the filter into WHERE:

-- example: where-filter
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
WHERE m.team = 'core'
ORDER BY e.id;

This result only retains employees with a matching manager from core. Rows with NULL in m.team do not satisfy the equality and are removed. The effect depends on the predicate: not every WHERE condition changes an outer join in the same way. For example, a condition looking for m.id IS NULL would select the unmatched rows instead.

The distinction between ON and WHERE is also explained in the PostgreSQL documentation. I use that as an explanatory reference; the examples in this article were executed in SQLite, without claiming a PostgreSQL execution test.

Unique pairs of people in the same team

A self join is also useful when there is no manager reference column. I want each pair of people from the same team exactly once:

-- example: pairs
SELECT a.id AS first_id, b.id AS second_id, a.team
FROM employees AS a
INNER JOIN employees AS b
    ON a.team = b.team AND a.id < b.id
ORDER BY a.id, b.id;

The condition a.team = b.team matches people from the same team. The condition a.id < b.id excludes a person paired with themselves and selects one of the two possible directions. We get 2-3, but not 3-2. This works for unordered pairs, where swapping the two sides does not change their meaning.

Using only a.id <> b.id would exclude self-pairs but retain both directions. Omitting the ID condition would produce nine combinations for a three-person team, including three people paired with themselves. Decide whether your problem concerns a directed relationship or a pair without a distinguished first member.

For a team of n people, the number of unordered pairs is n × (n - 1) / 2. Three people produce three pairs; four people produce six. Large teams can produce large results. This is the number of rows implied by the problem, not a universal claim about a database engine’s speed. Check the query plan and execution time on your own data.

Limits of the example and a quick result check

One join shows one relationship level: an employee and their direct manager. It does not automatically traverse the entire hierarchy. Following an arbitrary number of levels requires another technique, such as a recursive CTE, along with rules for handling cycles.

Before using this pattern in a report, check the meaning of both aliases, the uniqueness of the matching key and the expected row count. Then add a case without a reference. If the report must cover every employee, that person’s row should still appear.

The examples use simple, unquoted identifiers as aliases. Single quotes mark text values such as 'core'. I do not rely on exceptions in some engines that allow aliases to be written like string literals.

If you are still consolidating join fundamentals, see the CROSS JOIN example. Comparing combination counts makes it easier to see what the ON condition actually restricts when a table is joined to itself.