SQL Self-Join Explained with a Simple Example
ملخص
Pixel shares a beginner-friendly guide to SQL self-joins, using an employee table to show how aliases connect each employee to their manager. It explains why LEFT JOIN keeps rows without a manager and highlights uses such as parent-child categories and company relationships.
DEV Community
SQL Self-Join Explained with the Simplest Example
A self-join is when you join a table to itself. The first time you see it, you might think "wait, JOIN the same table…?", but ⭐️ it's the go-to technique when parent-child or boss-subordinate relationships live in a single table. Example: an employees table (bosses are employees too) Bosses are also employees, so they're in the same employees table. id name manager_id (boss's id) 1 Yamada (Department Head) (none) 2 Sato 1 3 Suzuki 1 When you want to "list each employee alongside their boss's name", you join employees again under a different alias. SELECT
emp.name AS employee,
boss.name AS boss
FROM employees AS emp
LEFT JOIN employees AS boss -- ← join the same table under another alias
ON emp.manager_id = boss.id;
Result: employee boss Yamada (Department Head) (none) Sato Yamada (Department Head) Suzuki Yamada (Department Head) The key is the alias (AS) Since it's the same table, the trick is to give it aliases like AS emp / AS boss and treat them as separate tables. emp.name … the employee's own name boss.name … the boss's name Without aliases, there's no way to tell "which name?" Why LEFT JOIN? We want to keep people with no boss (like the department head) in the result, so we use LEFT JOIN. With INNER JOIN, the rows without a boss would disappear. Summary When parent-child or boss-subordinate relationships are in the same table, joining the table to itself under an alias lets you put "the row + its related row" on one line. That's a self-join. It shines in designs that keep "relationships between things of the same kind" in one table: employee × boss, category × parent category, company × its parent company, and so on.

Powered by Readybot.io