Joining A Table To Itself
A self join is a regular join where both sides are the same table. You give the table two different aliases so the engine can treat each copy as separate. Nothing special happens internally, it is just naming.
When You Need It
Self joins shine when rows relate to other rows in the same table:
- An employees table where each row has a manager id pointing to another employee.
- Finding pairs of products in the same category.
- Comparing a row to the previous day in a time series.
A Manager Example
To list each employee next to their manager, you join employees as e to employees as m where the employee manager id equals the manager id. The result pairs each worker with their boss in one row.
Watch For Duplicates
When pairing rows, add a condition like e id less than m id to avoid listing the same pair twice and to avoid pairing a row with itself.
Key idea
A self join joins a table to itself using two aliases so you can compare or link rows that reference other rows in the same table.