SQL SELF JOIN

Every join so far has combined two different tables. A self join combines a table with itself, genuinely useful whenever one row needs to reference another row in the exact same table, like employees.manager_id pointing back at another row in employees.

Learning Objectives

• Recognize when a self-referencing column (like manager_id) calls for a self join.
• Write a self join using two different aliases for the same table.
• Use LEFT JOIN in a self join to include rows with no self-match, like a CEO with no manager.

Pairing Employees With Their Manager

employees is named twice, aliased as e (the employee being looked at) and m (whoever that employee's manager_id points to). Two different aliases for the same table are exactly what makes this a self join rather than a regular join, SQL itself doesn't need a special keyword for it, it's just an ordinary JOIN where the table on both sides happens to be the same one.

SELECT e.first_name AS employee_name, m.first_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Including Employees With No Manager

Plain JOIN here would silently drop whoever has a NULL manager_id, typically the one person at the top with no manager at all. LEFT JOIN keeps that row, showing manager_name as NULL for them instead of removing them from the result entirely, the exact same NULL-preserving behavior covered in the LEFT JOIN lesson, just applied to a table joined with itself.

SELECT e.first_name AS employee_name, m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Finding Everyone Who Shares a Manager

This pairs up every two employees who report to the same manager. e1.id < e2.id is there specifically to avoid listing both (Ada, Grace) and (Grace, Ada) as two separate rows, and to stop an employee from being paired with themselves, a common self-join pattern worth recognizing even if its exact use here is secondary to the manager-pairing example above.

SELECT e1.first_name AS employee_1, e2.first_name AS employee_2
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.id < e2.id;

Try It Yourself

Write a query listing every employee's name next to their manager's name, including anyone with no manager.

SELECT e.first_name AS employee_name, m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Always alias both sides of a self join, there's no way to write one correctly without aliases.
• Choose aliases that describe the role (e / m for employee / manager), not just arbitrary letters, for a reader scanning the query later.
• Use LEFT JOIN rather than plain JOIN whenever rows with no self-match (like a top-level manager) need to stay in the result.

Interview Questions

Summary

A self join joins a table to itself using two different aliases, the standard way to work with a self-referencing column like manager_id. LEFT JOIN keeps rows with no self-match, like whoever has no manager at all.

What's Next?

That completes Module 4, Joins: why Beacon Robotics' schema normalized into separate tables, INNER/LEFT/RIGHT/FULL JOIN, and self joins. Module 5, Combining and Grouping, is next, starting with UNION, the exact tool used to simulate FULL JOIN a few lessons back, now covered properly on its own.