Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Joins and Subqueries

SQL self join

Understand the SQL self join: join a table to itself with aliases to match employees with managers, compare rows in the same table and avoid duplicate pairs.

A self join is not a separate kind of join. It is an ordinary join in which both sides happen to be the same table. You list the table twice and give each occurrence its own alias, so the database treats them as two independent inputs.

You need a self join whenever a row has to be compared with, or linked to, another row of the same table: an employee and their manager, a product and a cheaper product in the same category, two people in the same department, a station and the next station on a line.

SQL
SELECT e.first_name, m.first_name AS manager
FROM employees AS e
JOIN employees AS m
  ON m.id = e.manager_id;

Try it

SQL Employees and their managers ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Pairs in the same department, each pair once ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Who earns more than their own manager? (nobody: no rows) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In the third example the result is empty: in this data nobody earns more than their manager. An empty result is a valid answer, not an error.

The idea: one table, two roles

Think of the two aliases as two roles the table plays. In the first example, e is "the employee" and m is "the manager". The ON condition connects a column of one role (e.manager_id) to a key of the other (m.id). Rows that have no partner, such as the CEO with a NULL manager_id, disappear with an inner join and are kept with a LEFT JOIN.

Aliases are mandatory here. Without them every column name would be ambiguous, and most databases reject the query. Choose names that describe the roles (e and m, emp and boss), not a and b, unless the roles really are symmetric.

Hierarchies and levels

One self join climbs one level: employee to manager. To get the manager's manager, join once more with a third alias. For trees of unknown depth, a self join is not enough; use a recursive CTE, see WITH RECURSIVE.

Pairs and duplicates

Comparing rows of the same kind produces symmetric pairs: (A, B) and (B, A). Joining on equality alone also pairs each row with itself. The fix is an inequality on the key. a.id < b.id removes self-pairs and keeps only one ordering of each pair, as in the second example. Use <> instead if you do want both orders.

Dialect differences

Self joins behave the same in all five major databases. The only variations are the usual ones: Oracle does not accept AS before a table alias (write employees e), and SQL Server's NATURAL JOIN does not exist, which does not matter here since self joins always use explicit conditions.

Usage notes

  • Choose the join type deliberately. Inner join drops rows without a partner; left join keeps them with NULLs in the second role.
  • Index the lookup column. The join key (usually the primary key and the foreign key such as manager_id) should be indexed on large tables.
  • Filter early. Conditions on either role go in WHERE, or in ON when the join is an outer join and the filter must not remove unmatched rows.
  • Counting direct reports is a self join with GROUP BY: LEFT JOIN employees r ON r.manager_id = m.id and COUNT(r.id).
  • Alternatives: window functions such as LAG() and LEAD() compare a row to its neighbours without a self join, and a correlated subquery can often express "better than the average of its group".