SQL – SELF JOIN

A self-join is when a table joins to itself. It sounds paradoxical, but it is essential for hierarchical data — like an employees table where each employee has a manager who is also an employee in the same table. You must use aliases to differentiate the two “copies” of the same table.

Self-joins are also useful for finding pairs — students in the same city, products in the same price range, or any situation where you are comparing rows within a single table.

Self-join mental model

-- employees table has: id, name, manager_id
-- manager_id references id in the SAME table
SELECT e.name AS employee,
       m.name AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.id;

Try It Yourself — Interactive SQL Editor

Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.

Key Points

• Self-join requires aliases (a and b) to tell the two copies apart
• Use a.id < b.id to avoid duplicate pairs in pairing queries
• Essential for hierarchical data: employees-managers, parent-children
• Can be used with any join type: INNER, LEFT, etc.

Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.

In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.