CHAPTER 16
Joining a table to itself — useful for hierarchical relationships.
🌟 Think of it this way: Imagine an employees table where each employee has a manager_id. To find who manages whom, we join the employees table with itself — one side is the employee, the other is the manager.
Shows each employee along with their manager name.
SQL
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id;
A table joined to itself. Our employees table has manager_id referencing emp_id in the same table:
Employees + their manager's name
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id ORDER BY e.name;
✅ Engineering Insight: INNER JOIN vs LEFT JOIN is a decision, not a preference. Ask yourself: 'Is it valid for a row to have no match?' If yes, use LEFT JOIN. If a row must have a corresponding entry in the other table (by business rules), use INNER JOIN — it will surface data integrity issues.
| customer_id | name | city | country |
|---|---|---|---|
| 1 | Ananya | Hyderabad | India |
| 2 | Rohan | Bangalore | India |
| 3 | Sam | Mumbai | India |
| 4 | Lisa | London | UK |
| 5 | Ravi | Delhi | India |
| order_id | customer_id | order_date | total | status |
|---|---|---|---|---|
| 1 | 1 | 2024-01-15 | 79999 | completed |
| 2 | 2 | 2024-01-20 | 3498 | completed |
| 3 | 1 | 2024-02-10 | 65000 | completed |
| 4 | 3 | 2024-02-14 | 499 | pending |
| 5 | 2 | 2024-03-01 | 248 | completed |
| 6 | 5 | 2024-03-15 | 2999 | shipped |
| 7 | 1 | 2024-04-05 | 149 | completed |