SQL Self Join: Read a One-Level Manager Relationship

The practice task

Show each fictional team member with their immediate manager. Retain the top-level member who has no manager, and do not mistake this one-level result for a full reporting chain.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE staff(id INTEGER, name TEXT, manager_id INTEGER);
INSERT INTO staff VALUES (1,'Dee',NULL),(2,'Ari',1),(3,'Bo',1),(4,'Cy',2);

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

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The employee alias supplies the output row. The manager alias finds the staff row named by manager_id. Dee remains with a missing manager because the join preserves employee rows. Ari and Bo point to Dee, while Cy points to Ari.

Aliases make the relationship readable even though both sets of values come from the same stored table. Joining e.id to m.id would simply match each member to themself, which would look structurally valid but would not follow the manager relationship.

This query follows one edge per employee. Finding every ancestor or detecting cycles requires a different traversal, often a recursive query with a clear stopping condition. Do not claim the direct-manager result proves that the entire hierarchy is valid or acyclic.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Name aliases after the roles each table instance plays.
• Join the reference key to the referenced identifier.
• Retain or exclude top-level records deliberately.