Joining a Table to Itself: Self Join

The Org Chart Question

HR asked: *"For each employee, can you show me who their manager is?"*

I looked at the `employees` table. It had a `manager_id` column. But the manager is also an employee in the same table! How do I join `employees` to `employees`?

The Quest: The Self Join

A **Self Join** is when you join a table to itself. You do this by giving the table two different aliases, treating it as if it were two separate tables.

The Implementation: The Syntax

SELECT

e.name AS employee_name,

m.name AS manager_name

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.id;

Why LEFT JOIN?

The CEO has no manager (`manager_id` is NULL). If we used INNER JOIN, the CEO would be excluded. LEFT JOIN keeps everyone.

Other Use Cases

**Referrals**: "Show me users and who referred them."

**Comparisons**: "Find all pairs of products with the same price."

**Hierarchies**: Anything with a parent-child relationship within the same entity.

The "Oops" Moment

I once forgot to use different aliases (`e` and `m`). SQL had no way of knowing which "employees" I was talking about.

**Pro Tip**: When self-joining, always give clear, descriptive aliases like `e` for employee and `m` for manager. It makes the query much easier to read.

The Victory

HR received a full org chart, pulled from a single table. They could see reporting lines, identify managers with too many direct reports, and plan restructuring. Self-join is a powerful, underrated technique.

Your Task for Today

Find a table with a parent-child relationship (like `manager_id` or `parent_category_id`) and write a self-join to show the hierarchy.

*Day 40: Generating All Combinations—CROSS JOIN.*