Lesson 8: Self Joins

A self join joins a table to itself — useful for hierarchical or comparative relationships within one table.

Real-time example

An employee table with a manager_id column: find each employee alongside their manager's name.

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

What's happening

The same physical table is referenced twice with different aliases (e and m) so the database treats them as two logical tables for the join.

Self joins are the standard pattern for org charts, category trees, and 'referred by' relationships.