selfjoins

A self join is a join operation where a table is joined to itself to relate rows within the same table. It is implemented by assigning two different aliases to the same table in the FROM clause, allowing each side of the join to refer to a distinct instance of the table.

The join condition specifies how the two aliases relate to each other. A common pattern is to connect a child row to its parent via a self-referential key, such as linking an employee to their manager using a manager_id column that references the employee_id of another row in the same table.

Common uses include representing hierarchical data in an adjacency-list model, such as employees and their managers, organizational structures, or bill-of-materials relationships. Self joins can also be used to compare rows within the same table, find pairs that share a property, or aggregate related rows.

Example:

SELECT e.name AS employee, m.name AS manager

FROM employees AS e

JOIN employees AS m ON e.manager_id = m.employee_id;

Performance considerations apply to self joins as to other joins. They can be expensive on large tables, so appropriate indexing on the join columns is important. In some cases, recursive common table expressions (CTEs) or specialized hierarchical queries may offer clearer or more scalable solutions for deep hierarchies. When writing self-joins, fully qualify column references with the correct alias to avoid ambiguity, and be mindful of null values in join keys, which can affect results.