Self Join: Comparing Rows Within One Table

Overview

A self join is just an ordinary join where a table is joined to itself, using different aliases. It's the standard way to model hierarchical or peer-to-peer relationships stored in a single table — most commonly an employee/manager relationship where manager_id points back at another employees.id.

The Query

Step-by-Step Breakdown

1. FROM employees e — alias e is the employee (the "child" side).
2. INNER JOIN employees m ON e.manager_id = m.id — alias m is the manager (the "parent" side), matched via the foreign key manager_id.
3. An INNER JOIN here excludes top-level staff whose manager_id is NULL (the department heads). Switch to LEFT JOIN to keep them with a NULL manager.

Variations

Show every employee with their manager, including those without one:

Common Mistakes

• Reusing the same alias. You must give the table two distinct aliases (e and m); referencing employees twice without aliases is a syntax error.
• Creating a cartesian product. Forgetting the ON e.manager_id = m.id condition pairs every employee with every other employee.
• Losing top-level rows. INNER JOIN drops employees whose manager_id is NULL — often exactly the people (executives) you most want to see.