Self Joins with Aliases in MySQL
A self join is when a table is joined with itself. To join the same table multiple times, you must use table aliases to differentiate each instance in the query.
• MySQL requires each table in a JOIN to have a unique reference.
• Aliases allow the same table to appear multiple times in the query.
• Aliases clarify which instance of the table each column belongs to.
• Representing hierarchical data (e.g., employees reporting to managers).
• Comparing rows within the same table (e.g., finding overlapping dates or relationships).
• Performing multiple joins to the same lookup table for different roles or attributes.
In this example, the employees table is joined twice using aliases m for manager and n for mentor. Each alias allows you to reference different relationships within the same table.
• Always assign a unique alias for each instance of the table.
• Use meaningful alias names to make the query readable.
• Self joins can be combined with INNER, LEFT, or RIGHT JOIN depending on which rows should be preserved.
In summary: Joining a table with itself multiple times requires aliases to differentiate each instance. This allows you to query hierarchical or relational data within a single table effectively.