How do I compare records within the same table using a self-join?

Join the employees table to itself. Think of the two appearances as two roles: e is the employee being listed, and m is the manager being looked up. The ON condition pairs a row where e.manager_id matches another row’s m.employee_id. A self-join uses the same table as both inputs with separate aliases, and the ON expression specifies which row pairs to include. [S1]

Assume this illustrative schema, where manager_id stores the employee ID of the manager:

employees(employee_id, name, manager_id)

Here is a query that returns employee-manager pairs when the IDs match:

SELECT
  e.employee_id AS employee_id,
  e.name AS employee_name,
  m.employee_id AS manager_id,
  m.name AS manager_name
FROM employees AS e
JOIN employees AS m
  ON e.manager_id = m.employee_id;

The schema and records below are invented for explanation; substitute your own table and column names.

Suppose the illustrative table contains these rows:

employee_id | name  | manager_id
1           | Amina | NULL
2           | Ben   | 1
3           | Chen  | 1

The query would produce these matched employee-manager pairs:

employee_id | employee_name | manager_id | manager_name
2           | Ben           | 1          | Amina
3           | Chen          | 1          | Amina

For Ben’s result, e refers to Ben’s row and m refers to Amina’s row: Ben’s manager_id is 1, and Amina’s employee_id is 1. The selected e columns describe the employee; the selected m columns describe the matching manager. Amina does not appear as an employee in this result because her manager_id is NULL, so it does not match an employee_id in the example. The sample is only a demonstration, not a claim about any real organization.

The query shown uses JOIN to return employee-manager row pairs that satisfy the ON condition. It therefore demonstrates the matching case, but it does not include an employee row such as Amina’s when there is no matching manager ID. The ON expression determines which candidate row pairs are included. [S1]

To choose a different join type for a report that must retain employees without a matching manager, check the documentation for your specific database and join type. The source-backed example here uses JOIN for matched pairs; the appropriate query depends on the result you want and the database you use. In either version, keep the relationship condition clear: e.manager_id = m.employee_id.

Start by checking the relationship the query assumes: an employee’s manager_id should match the manager row’s employee_id. Confirm that those are the intended ID columns and that the expected manager row is present. The example’s ON condition is specifically what pairs rows whose IDs match. [S1]

To inspect which rows are being paired, temporarily include both IDs in the output, as the query does with e.employee_id and m.employee_id. Also label columns from each alias separately, such as e.name AS employee_name and m.name AS manager_name, so you can distinguish the two roles. If the results still do not fit your data, check your actual table structure and database documentation; the illustrative schema cannot establish your system’s data rules.