intermediate

SELF JOIN

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

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

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

Note

Interview Question: Write a query to find all employees who earn more than their direct manager.

sql
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

Challenge 1: Compare Employee Salaries to Manager

Select employee 'name' from 'employees' (e) joined with 'employees' (m) on 'e.manager_id = m.id' where 'e.salary > m.salary'.


Summary

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

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.