Joining A Table To Itself
A self join treats one table as if it were two, using table aliases so each copy can be referenced separately. This is how you express relationships that live inside a single table.
When You Need It
- Hierarchies like an employees table where each row has a manager id pointing to another row in the same table.
- Pairs such as finding two products in the same category with different prices.
- Sequences like comparing each event to the previous event of the same user.
How It Works
- Give the table two aliases, for example e for employee and m for manager.
- Write the join condition that links them, such as e manager id equals m id.
- Select columns from both aliases to show the relationship in one row.
Watch For Duplicates
When pairing rows together you often get each pair twice in mirror order. Adding a condition like one id less than the other id keeps a single copy and avoids matching a row with itself.
Key idea
A self join aliases one table as two copies so you can relate rows to each other, and a comparison on ids removes mirror duplicates.