SELF JOIN in SQL

A SELF JOIN joins a table to itself — used when rows in a table relate to other rows in that same table, like an employee and their manager (who is also an employee).

Sample Table: employees (with a manager reference)

Aman has no manager (he's the top). Riya and Karan report to Aman. Neha reports to Riya.

Syntax

There's no special SELF JOIN keyword — you use a regular join, but reference the same table twice under two different aliases:

SELECT a.column, b.column
FROM table_name a
JOIN table_name b ON a.some_column = b.some_column;

Example — Employee and Their Manager's Name

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

Expected Output:

A LEFT JOIN is used here (not INNER JOIN) so that Aman — who has no manager — still appears in the result.

Why the Aliases Are Required

Since both sides of the join come from the same physical table, SQL needs a way to tell them apart. e and m here represent "the employee row" and "the manager row" respectively — same table, two different roles in this query.

Practical Use Case

Any hierarchical or relational structure stored flat in one table: org charts (employee → manager), category trees (subcategory → parent category), or finding pairs within the same table (e.g. products in the same category with different prices).

Common Mistakes

• Forgetting the aliases entirely — employees JOIN employees ON ... is ambiguous and will error
• Using INNER JOIN when top-level rows (with no self-reference, like Aman here) need to still appear — use LEFT JOIN instead
• Confusing which alias plays which role in the ON condition, especially in queries with more than two conditions

Interview Relevance

"Find each employee's manager's name" or "find employees who earn more than their manager" are classic self-join interview questions — practice both until the aliasing pattern feels automatic.

Practice Question

Using the table above, write a query to find employees who earn more than their manager (assume both have a salary column).

Related SQL Notes

• LEFT JOIN
• Aliases
• SQL Joins Overview