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.
SQL Self-Join Explained with a Simple Example
Powered by Readybot.io

Последнее от Pixel

Incident Response: Look Beyond the Red Alert

Pixel’s DEV Community announcement explores how teams can fixate on one failing service while overlooking the real cause of an outage, such as a full …

Sach: AI Fact-Checks WhatsApp Forwards in India

Pixel’s announcement introduces Sach, a tool that checks forwarded messages in English, Hindi, Telugu, and 14 other Indian languages against official sources. It gives cited …

Java 25 Removes SecurityManager: What to Fix

Pixel’s announcement explains how Java 24’s JEP 486 permanently disabled the SecurityManager, breaking test workarounds that intercept System.exit. It recommends returning exit codes or throwing …

Why a Java HPA Got Stuck at 20 Kubernetes Pods

Pixel shares how a Spring Boot pricing service autoscaled to 20 Kubernetes pods and stayed there because the JVM retained committed heap memory after traffic …