SELF JOIN

Overview

A SELF JOIN is a regular join in which a table is joined with itself.

Learning Objectives

• Use SELF JOIN to query hierarchical or graph data (e.g. employee-manager hierarchies).
• Differentiate row instances of the same table using distinct aliases.
• Compare records within the same table (e.g. price changes over time).

Detailed Concept Explanation

A SELF JOIN is not a special SQL keyword; it simply means referencing the same table twice in the FROM and JOIN clauses, assigning distinct aliases to treat them as two separate logical tables.

Code Examples

SQL

-- Find employee names along with their manager's name
SELECT 
  e.name AS employee_name,
  m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Find pairs of products in the same category
SELECT 
  p1.product_name AS product_1,
  p2.product_name AS product_2,
  p1.category
FROM products p1
JOIN products p2 ON p1.category = p2.category AND p1.product_id < p2.product_id;

Best Practices

• Prevent Self-Matching: When matching items within the same group, add an inequality condition like p1.id < p2.id to avoid pairing a row with itself and to avoid duplicate reversed pairs.

Interview Perspective

SELECT e.name AS employee_name, e.salary, m.name AS manager_name, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

Interactive Challenges

Summary

SELF JOIN joins a table to itself using distinct aliases, commonly used for hierarchies and intra-table row comparisons.