Introduction to Self-Joins
You'll learn what a self-join is and why you'd ever join a table to itself.
What Is a Self-Join?
A self-join is when you join a table to itself—treating the same table as if it were two separate tables in your query. This might sound strange at first: why would you need to compare a table with itself? The answer lies in finding relationships between different rows in the same table.
Why Self-Join?
Self-joins are powerful when your data contains hierarchical or relational patterns within a single table. Common scenarios include:
- Employee-manager relationships: An
employeestable where each employee has amanager_idpointing to another employee in the same table - Product recommendations: Finding pairs of products from the same
productstable - Sequential events: Comparing different timestamps or events within a single
eventstable
Think of it like looking in a mirror: you're still you, but now you can observe yourself from a different angle. Similarly, a self-join lets you examine one set of rows against another set from the same table.
The Key: Table Aliases
Self-joins are only possible because of table aliases. You give the same table two different temporary names (aliases), allowing SQL to treat them as distinct entities during the join:
FROM employees AS e1
INNER JOIN employees AS e2
ON e1.manager_id = e2.employee_id
Here, e1 and e2 both refer to employees, but SQL treats them as separate "copies" for joining purposes.
Key Takeaway: A self-join joins a table to itself using aliases, enabling you to find relationships between different rows within the same table—essential for hierarchical data and row-to-row comparisons.