SQL SELF JOIN and Its Real-World Use Cases

🔹 What Is a SELF JOIN?

A SELF JOIN joins a table to itself. It is ideal for modelling hierarchical or comparative relationships stored in a single table—such as employees and their managers, products and sub-categories, or pairwise comparisons.

⚙️ Basic Syntax

SELECT
  a.column_name,
  b.related_column
FROM table_name a
JOIN table_name b
  ON a.common_field = b.related_field;

Alias names (a and b) create two logical copies of the same table so that you can compare rows to each other.

🧠 Example 1 – Employee ↔ Manager Mapping

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

The LEFT JOIN keeps top-level employees (with NULL managers) in the output.

🧠 Example 2 – Compare Salaries Within One Table

SELECT
  e1.emp_name AS employee,
  e2.emp_name AS lower_paid_employee
FROM employees e1
JOIN employees e2
  ON e1.salary > e2.salary;

🧠 Example 3 – Find Duplicate Records

SELECT
  s1.id,
  s1.name,
  s1.email
FROM students s1
JOIN students s2
  ON s1.email = s2.email
  AND s1.id <> s2.id;

This pattern detects duplicated values by matching rows with the same email but different IDs.

🧩 Example 4 – Parent and Sub-Category Mapping

SELECT
  c1.category_name AS parent_category,
  c2.category_name AS sub_category
FROM categories c1
JOIN categories c2
  ON c1.category_id = c2.parent_id;

Great for traversing product hierarchies or building breadcrumb navigation.

⚙️ Key Use Cases

• Employee → manager hierarchies
• Product categories and sub-categories
• Duplicate detection across rows
• Comparing values within the same table
• Visualising hierarchical data without separate tables

⚖️ Tips for Reliable SELF JOINs

• Always use aliases to distinguish each instance of the table.
• Choose LEFT JOIN when you must keep unmatched parent rows.
• Add filters to avoid matching a row with itself (a.id <> b.id).

💡 Interview Soundbite