Self Join
What you'll learn: How to compare rows within the same table by joining it to itself using table aliases.
What Is a Self Join?
A self join is when you join a table to itself — essentially treating the same table as if it were two separate tables. You do this by using table aliases to give the same table two different temporary names in your query.
Why would you do this? Sometimes the data you need to compare or combine lives in the same table. For example, if you have an employees table with a column for manager ID, both the employee and their manager are in the same table. A self join lets you match employees with their managers.
How It Works
You use the same table name twice, but assign different aliases (like e1 and e2) to distinguish between the two "copies." Then you join them based on a relationship between rows.
Example: Finding employees and their managers:
SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
Here, e1 represents employees, and e2 represents their managers. The join condition matches each employee's manager_id to another employee's employee_id.
Common Use Cases
- Hierarchical relationships: Employees and managers, categories and parent categories
- Comparing rows: Finding pairs of products in the same price range
- Sequential data: Matching consecutive records (e.g., comparing today's sales to yesterday's)
Analogy
Think of it like looking at a family tree where parents and children are all listed in one notebook. To see parent-child pairs, you read through the notebook twice simultaneously — once for children, once for parents — and match them up.
Key Takeaway: A self join uses table aliases to join a table to itself, enabling comparisons and relationships between rows in the same table.