VocalHyre
Sign up

SQL · Queries that survive scale

What is a self-join and when do you use one?

Mediumself joinhierarchiesaliasesUpdated Oct 2026
Quiz this track

Short answer

A self-join joins a table to itself using two aliases. It is useful for comparing rows in the same table, such as employee to manager or adjacent events.

A self-join treats one table as two logical inputs. Each instance needs an alias so the database can distinguish the columns. The join predicate then relates rows within the same table.

sql
SELECT e.id AS employee_id,
       e.name AS employee_name,
       m.id AS manager_id,
       m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY m.name, e.name;
The LEFT JOIN keeps employees without a manager, such as the CEO.

Typical uses

  • Walking a one-level hierarchy such as employee to manager
  • Comparing a row with the previous or next row by date
  • Finding duplicate records by matching on normalized fields
  • Building pairs of related rows, such as users in the same team

A self-join only follows a fixed number of levels. For arbitrary depth, such as an entire org chart, use a recursive CTE. For large tables, index the join column and watch for row multiplication.

Forgetting an alias causes an ambiguous column error or an accidental Cartesian product. Qualify every column when the table appears more than once.

Follow-ups you should expect

Interviewers use these to check whether the first answer was understanding or memorisation.

  1. 1How would you fetch an entire org chart instead of one level?
  2. 2How do you handle employees with no manager?
  3. 3What is the risk of a self-join on a large table?
  4. 4How do you find duplicate rows using a self-join?

Say it in the interview

A self-join joins a table to itself using two aliases, which lets me relate rows in the same table. I use it for one-level hierarchies, comparing adjacent rows, or finding duplicates. I alias both sides and qualify every column. If I need arbitrary depth, I switch to a recursive CTE instead of adding more self-joins.

Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.

Related questions

EasyHow do joins work in SQL?HardHow does a recursive CTE work?MediumWhat is a CTE and why would you use one?

Reading is step one

Now say it out loud.

Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.

Start a free SQL interviewBrowse all questions