SQL Practice Lesson 19 of 26

Self-join: a table joined to itself

Every join so far has connected two different tables. Sometimes the row you need to match against lives in the same table - an employee's manager is just another employee, stored in the very same employees table with the very same columns. You can still JOIN, you just join the table to itself.

The trick is aliases: give the table two different names so the database (and you) can tell "the employee" apart from "their manager" even though both sides are pulling from employees.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

What just happened: e and m are the same table, employees, aliased twice. e.manager_id = m.id matches each employee row (e) to the row (m) whose id equals that manager id - same mechanics as any other join, just with both sides pointed at one table.

There's an employees table: id, name, manager_id (which points at another row's id in the same table; the top of the org chart has manager_id set to NULL).

Your task: return each employee's name alongside their manager's name.

You'll practice:

Joining a table to itself using two different aliases
Matching a self-referencing foreign key (manager_id) back to the table's own id

-- Return each employee's name alongside their manager's name.
SELECT * FROM employees;