Self Joins

So far, we’ve joined a table to a different table (like users to orders). But occasionally, the relationship you need to resolve exists within the exact same table.

When you join a table to itself, it’s called a Self Join.

Why Join a Table to Itself?

The most classic example of a Self Join is an employee hierarchy.

Imagine an employees table with an id, a name, and a manager_id. The manager_id is a Foreign Key… but it points right back to the id column of the same employees table!

If we want to generate a report showing “Employee Name | Manager Name”, we need to look at the employees table twice in the same query.

The Syntax

To perform a Self Join, you write a standard INNER JOIN or LEFT JOIN, but you list the same table on both sides.

Because you are using the same table twice, you MUST use table aliases, otherwise the database engine won’t know which version of the table you’re referring to.

SELECT 
  employee.name AS employee_name, 
  manager.name AS manager_name
FROM employees AS employee
INNER JOIN employees AS manager
  ON employee.manager_id = manager.id;

Notice how we mentally (and virtually) split the single employees table into two distinct entities: an employee table and a manager table. Then we told the database to match the manager_id from the left side against the id on the right side.

[!TIP] If you used a LEFT JOIN instead of an INNER JOIN in the example above, the CEO (who has no manager and a manager_id of NULL) would be included in the results with a NULL manager name.

Try writing a Self Join in the interactive lab to map out the organizational chart of our E-Commerce company!