SELF JOIN
Overview
A SELF JOIN is a regular join in which a table is joined with itself.
Learning Objectives
- Use
SELF JOINto 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.idto 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.
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.
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.