Understanding SELF JOIN in MySQL
A SELF JOIN is a join in which a table is joined with itself. This is useful when rows in the same table have a relationship with one another — such as hierarchical data, parent-child relationships, or comparing rows within the same table.
• When a table contains a hierarchical structure (e.g., employees reporting to managers).
• To compare rows within the same table (e.g., finding people who live in the same city).
• To model parent-child relationships within a single table.
• Whenever you need to relate rows from the same dataset.
Suppose you have an
employeestable:• id
• name
• manager_id (references employees.id)
• Here, the table employees is used twice: once as e (employee) and once as m (manager).
• The result lists each employee with their corresponding manager.
• Uses table aliases to differentiate between the two instances of the same table.
• Can be used with INNER, LEFT, or RIGHT JOIN depending on the requirement.
• Essential for hierarchical or relational data stored in one table.