What is a self join? | Interview Question

Q: What is a self join?

**Target Audience:** Beginners who know a basic join and want to see a table joined to itself, and candidates preparing for the follow-ups. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Self joins are plain standard SQL and work the same way in MySQL and SQL Server. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Imagine the company phone list printed on one sheet. Each line has a person, their job, and a column "reports to: #2". To answer "who is Omar's boss?" you find Omar, read the number 2, then run your finger back up the *same sheet* to find line 2: Raj. Now imagi

The problem

Question presented to candidate: "Our employees table has a manager_id column that points at another row of the same table. Write a query that lists every employee next to their manager's name, and explain what a self join is and when you would reach for one."

What a strong answer should cover:

A self join is an ordinary join (INNER, LEFT, and so on) in which both sides are the same table, so each copy needs its own alias
The typical use: a self-referencing foreign key such as manager_id REFERENCES employees(id), the adjacency list way of storing a hierarchy
LEFT JOIN keeps the root row (the CEO, whose manager_id is NULL); INNER JOIN silently drops it
Other patterns: comparing a row to a related row (earns more than the manager), and building pairs inside a group with a.id < b.id to avoid self-pairs and mirror duplicates
Limits: one self join walks exactly one level; an unknown depth needs a recursive CTE
Performance: the engine treats the two aliases as two independent inputs, so the join column (manager_id) wants an index like any other join

Code / implementation expected: Yes — an employee/manager query with two aliases of the same table, and ideally one comparison or pairing query.

Schema used by the examples:

self-join

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know a basic join and want to see a table joined to itself, and candidates preparing for the follow-ups. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Self joins are plain standard SQL and work the same way in MySQL and SQL Server.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Imagine the company phone list printed on one sheet. Each line has a person, their job, and a column "reports to: #2". To answer "who is Omar's boss?" you find Om

The solution is hidden so you can try first

10 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLEvery employee next to their manager (LEFT JOIN keeps the CEO)

SQLCompare a row with a related row: who earns more than their manager?

SQLPairs of colleagues in the same department, each pair once

PostgreSQL: the plan reads the same table twice, as two inputs

EXPLAIN (COSTS OFF)
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.