Self Join

A self join is a join where a table is joined to itself, using two aliases to treat it as if it were two separate tables. It's used to compare or relate rows within the same table.

The classic example is an employee/manager relationship stored in a single table: employees has a manager_id column that points back to another row's employee_id. Aliasing the table twice (e for the employee, m for the manager) lets you join it to itself.

A self join can be an INNER JOIN or a LEFT JOIN depending on whether rows with no self-match (like a company president with no manager) should be kept.

Example

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;