SQL self join: compare rows in the same table

Use two aliases for the same table: employees e JOIN employees m ON m.id = e.manager_id.

Use LEFT JOIN when some rows have no partner, such as the CEO who has no manager.

Worked example

Who reports to whom?

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

Common mistakes

• Reusing one alias for both sides.
• Using INNER JOIN and losing the rows without a partner.