CodeRang
SQL topics

SQL Self Join: How to Join a Table to Itself

SQL self join matches rows from a table with other rows in that same table. This example uses separate aliases to compare employees with their managers and shows how the join condition controls each match.

Updated · SQL Arena team

Interview preparation trackAll problems

What you will practice

  • Join one table to itself with clear aliases
  • Match an employee row to a manager row
  • Choose INNER JOIN or LEFT JOIN for self joins
  • Build unique pairs with a.id < b.id
  • Keep alias roles consistent across SELECT, ON, and WHERE

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Practice task: employees earning more than their manager

In employees, manager_id points to another row's id. Return the employee, the direct manager, and the employee salary only when the employee earns more than that manager.

Query 1PostgreSQLv16
SELECT e.employee_name AS employee,
       m.employee_name AS manager,
       e.salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary
ORDER BY e.salary DESC, e.employee_name;
Submit
Resultemployeesreviews
Answer accepted

How an SQL Self Join Works

The query references employees twice. Alias e represents the employee row and alias m represents the manager row. They are not copies of the data; they are two roles assigned to the same table inside one query. Qualifying columns with aliases is essential here because names such as id, employee_name, and salary exist on both sides of the join.

The join condition e.manager_id = m.id follows the relationship stored in the table. If Leo has manager_id = 1, his row is matched with Maya's row where id = 1. A regular JOIN is appropriate because this task only needs employees who have a matching manager. If the report had to keep top-level employees with no manager, LEFT JOIN would preserve them and return NULL for manager columns.

Once the rows are matched, WHERE e.salary > m.salary compares values from the two roles. Quinn, Nora, and Pia pass that test in the sample data. The final ORDER BY sorts by employee salary and then by name, so the output is deterministic rather than dependent on whichever row order PostgreSQL happens to produce.

SQLPostgreSQL v16
SELECT e.employee_name AS employee,
       m.employee_name AS manager,
       e.salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary
ORDER BY e.salary DESC, e.employee_name;
Query result
employeemanagersalary
QuinnNora130000
NoraMaya125000
PiaLeo98000

Common SQL Self Join Mistakes

01

Joining id to id

A condition such as e.id = m.id only matches each employee to the same row. For an employee-manager hierarchy, join the relationship column to the referenced key: e.manager_id = m.id.

02

Reversing alias roles

Changing e.salary > m.salary to m.salary > e.salary answers a different question. Pick role-based aliases and keep them consistent throughout the query.

03

Creating mirrored pairs

For pair-generation problems, a.id <> b.id produces both A-B and B-A. Use a.id < b.id when the order of the two rows does not matter and each pair should appear once.

Practice problems on this topic

Interview preparation track

A useful progression is to move from hierarchy matching to pair generation. The free task about unique category-supplier pairs trains duplicate control, while the employee-versus-manager task trains a direct self-reference. Harder exercises extend the same idea to reporting structures, including direct-report maps and manager counts. If you are preparing for technical interviews, continue with the “Interview Preparation” track. SQL Arena contains 1,192 tasks, and answers are checked against hidden data rather than only the visible sample.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is an SQL self join?

An SQL self join joins a table to another logical instance of itself. Different aliases let the query treat rows from the same physical table as separate roles.

How do you self join a table in SQL?

Reference the table twice with different aliases and write an ON condition that connects the two roles. For an employee hierarchy, an employee's manager_id can be joined to another row's id.

Why are aliases required in an SQL self join?

Both sides expose columns with the same names, so aliases identify which logical instance each column belongs to. They also make the relationship between the two row roles explicit.

Can an SQL self join use LEFT JOIN?

Yes. LEFT JOIN is useful when rows without a matching related row should remain in the result, such as top-level employees who do not have a manager.

See also

Practice SQL Self Join Queries

Work through self join tasks in SQL Arena, from employee-manager matching to duplicate-free pairs. The free plan includes 292 tasks across PostgreSQL, MySQL, ClickHouse, and BigQuery with no card required.

Practice self joins

More practice from CodeRang