Self Join

A self join joins a table to itself - useful when rows in a single table need to be compared or related to other rows in that same table, such as employees and their managers, both stored in one employees table.

Basic Syntax

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

Here, the same employees table is used twice with different aliases (e and m) - once to represent the employee, once to represent that employee's manager.

Why Aliases Are Required

Since a self join references the same table twice, SQL requires aliases to distinguish between the two "copies" - without them, the database wouldn't know which instance of a column you mean.

Other Use Cases

• Finding pairs of products frequently bought together within an order
• Comparing each row to the row before or after it (e.g. month-over-month changes)
• Identifying duplicate records within a table