ADVERTISEMENT

SQL SELF JOIN

A SELF JOIN is a regular JOIN where a table is joined with itself to compare rows within the same table.

It’s commonly used for hierarchical data, like employees and managers, or products and related products.

🔹 Basic Syntax

SELECT A.column1, B.column2
FROM table_name A
JOIN table_name B
  ON A.common_column = B.common_column;

🔹 Example: Employees and Their Managers

Assume each employee has a manager_id referring to another employee’s employee_id in the same table.

SELECT
  e.employee_name AS employee,
  m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.employee_id;

✅ This shows each employee and their manager’s name.

🔹 When to Use SELF JOIN

  • Compare rows in the same table
  • Analyze hierarchical or paired data
  • Find duplicate or related records

🧠 Quick Recap

Key PointExplanation
SELF JOINA table joins with itself
Use caseHierarchies, comparisons, duplicates
AliasesRequired to distinguish the same table used twice
JOIN typeCan be INNER, LEFT, etc., depending on the need

🔁 Use SELF JOIN for comparing or relating data within a single table

ADVERTISEMENT