Beginner

526. Self JOIN

Learn how a table can be joined to itself to compare or connect rows within the same table.

📘 Lesson 526 of 728 ⏱ 12 min 🎯 Intermediate

A self JOIN occurs when a table is joined to itself using different aliases so that rows within the same table can be compared or related.

Hierarchical and comparison-based data is often stored in a single table, making self JOINs useful for analysis.

An Employees table can contain ManagerID, allowing employees to be joined to their managers.

SQL 
SELECT
    e.EmployeeName AS Employee,
    m.EmployeeName AS Manager
FROM Employees e
LEFT JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

Used for employee hierarchies, organizational structures, category trees, and row comparisons.

  • A self JOIN joins a table to itself.
  • Different aliases are required.
  • Self JOINs are useful for hierarchical data.
  • Manager-employee relationships are a common example.
  • NULL managers can be preserved with LEFT JOIN.
What is a self JOIN?

A self JOIN joins a table to itself using different aliases.

Where is a self JOIN commonly used?

It is commonly used for employee-manager hierarchies and other hierarchical relationships.

Have you finished this lesson?

Introduction to SQL – All Lessons