Self Joins — Comparing Rows Within the Same Table

---

title: “Self Joins” part: 2 topic_number: 3 slug: “self-joins” difficulty: “Intermediate” prerequisites: “inner-join, left-join” —

Self Joins — Comparing Rows Within the Same Table

What Is It?

A self join is when a table is joined to itself. It’s used to compare rows within the same table, like finding employees who earn more than their colleagues, or discovering hierarchical relationships (managers and their reports).

Real-world analogy: Like having a conversation with yourself in the mirror — you’re both the speaker and the listener.

When you’d use it:

• Find employees managed by the same manager
• Compare records (e.g., employees earning more than average in their department)
• Hierarchical relationships (org charts, categories with subcategories)
• Find duplicates or related records

---

Syntax Breakdown

SELECT ...
FROM table AS alias1
JOIN table AS alias2 ON alias1.column = alias2.column
WHERE ...

Key points:

• Same table appears twice in FROM clause
• Different aliases (alias1, alias2) distinguish the two “copies”
• Join condition relates rows to each other
• Can use INNER, LEFT, RIGHT joins (just like regular joins)

---

Basic Examples

Find Employee Pairs in Same Department

First, let’s add a manager_id column to employees (simulating an org structure):

-- Setup: Add manager relationships (for demonstration)
ALTER TABLE employees ADD COLUMN manager_id INT NULL;

UPDATE employees SET manager_id = 1 WHERE employee_id IN (3, 5, 7);
UPDATE employees SET manager_id = 2 WHERE employee_id IN (4, 6);
UPDATE employees SET manager_id = 10 WHERE employee_id IN (12, 14);

Now, find pairs of employees with the same manager:

SELECT 
    e1.first_name AS employee1_name,
    e2.first_name AS employee2_name,
    e1.manager_id
FROM employees e1
JOIN employees e2 
    ON e1.manager_id = e2.manager_id 
    AND e1.employee_id < e2.employee_id  -- Avoid duplicates
WHERE e1.manager_id IS NOT NULL
ORDER BY e1.manager_id, e1.first_name;

Expected output:

employee1_name	employee2_name	manager_id
Carol	Eve	1
Carol	George	1
Eve	George	1
David	Frank	2

How it works:

• e1 and e2 are both “employees” table
• Join where they have the same manager
• e1.employee_id < e2.employee_id prevents seeing (Carol, Eve) and (Eve, Carol) as separate pairs

Show Manager Names

SELECT 
    e.employee_id,
    e.first_name AS employee_name,
    e.last_name AS employee_lastname,
    m.first_name AS manager_name,
    m.last_name AS manager_lastname
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.last_name, e.last_name
LIMIT 10;

Expected output:

employee_id	employee_name	employee_lastname	manager_name	manager_lastname
3	Carol	Williams	Alice	Johnson
5	Eve	Davis	Alice	Johnson
7	George	Jones	Alice	Johnson
4	David	Brown	Bob	Smith
6	Frank	Miller	Bob	Smith
1	Alice	Johnson	NULL	NULL
2	Bob	Smith	NULL	NULL
…	…	…	…	…

Each employee with their manager’s name (NULLs are top-level managers with no boss).

---

Going Deeper

Find Employees Earning More Than Their Manager

SELECT 
    e.first_name AS employee_name,
    e.salary AS employee_salary,
    m.first_name AS manager_name,
    m.salary AS manager_salary,
    (e.salary - m.salary) AS salary_difference
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary
ORDER BY salary_difference DESC;

Expected output:

employee_name	employee_salary	manager_name	manager_salary	salary_difference
Frank	93000.00	Bob	82000.00	11000.00
George	71000.00	Alice	75000.00	-4000.00

Employees who out-earn their managers (might indicate promotion potential!).

Hierarchical Listing (2 Levels)

SELECT 
    m.first_name AS manager,
    m.salary AS manager_salary,
    COUNT(e.employee_id) AS direct_reports,
    AVG(e.salary) AS avg_report_salary
FROM employees m
LEFT JOIN employees e ON m.employee_id = e.manager_id
WHERE m.manager_id IS NULL  -- Top-level managers only
GROUP BY m.employee_id, m.first_name, m.salary
ORDER BY m.first_name;

Expected output:

manager	manager_salary	direct_reports	avg_report_salary
Alice	75000.00	3	74333.33
Bob	82000.00	2	79500.00

Top-level managers with their team statistics.

Find Salary Gaps

Find employees whose salary is more than $10k different from colleagues in the same department:

SELECT DISTINCT
    e1.first_name,
    e1.last_name,
    e1.salary AS their_salary,
    e1.department_id,
    e2.first_name AS colleague_name,
    e2.salary AS colleague_salary,
    ABS(e1.salary - e2.salary) AS salary_gap
FROM employees e1
JOIN employees e2 
    ON e1.department_id = e2.department_id 
    AND e1.employee_id != e2.employee_id
WHERE ABS(e1.salary - e2.salary) > 10000
ORDER BY e1.department_id, salary_gap DESC;

Expected output:

first_name	last_name	their_salary	department_id	colleague_name	colleague_salary	salary_gap
Sam	Clark	95000.00	1	Alice	75000.00	20000.00
Paul	Garcia	87000.00	1	Alice	75000.00	12000.00
…	…	…	…	…	…	…

Identifies significant pay disparities within departments.

Pause and Predict: Why do we use e1.employee_id != e2.employee_id instead of <?

Answer

`<` would only show one direction of the comparison (e.g., Alice compared to Bob, but not Bob compared to Alice). `!=` ensures we don't compare an employee to themselves, but we see both directions. If you want to avoid duplicates (both Alice->Bob and Bob->Alice), use `e1.employee_id < e2.employee_id`.

---

Watch Out — Common Mistakes

Mistake #1: Forgetting Different Aliases

--  ERROR (no aliases)
SELECT *
FROM employees
JOIN employees ON ...

Error: “Not unique table/alias: ‘employees’”

Fix: Always use different aliases:

-- • CORRECT
SELECT *
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

Mistake #2: Not Preventing Self-Matches

--  LOGIC ERROR
SELECT e1.first_name, e2.first_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id;

Problem: Each employee matches with themselves (Alice appears with Alice).

Fix: Add a condition to exclude self-matches:

-- • CORRECT
SELECT e1.first_name, e2.first_name
FROM employees e1
JOIN employees e2 
    ON e1.department_id = e2.department_id 
    AND e1.employee_id != e2.employee_id;

Mistake #3: Getting Duplicate Pairs

--  Shows (Alice, Bob) and (Bob, Alice)
SELECT e1.first_name, e2.first_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id != e2.employee_id;

Problem: Both directions of each pair appear.

Fix: Use < instead of !=:

-- • CORRECT (each pair appears once)
SELECT e1.first_name, e2.first_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id < e2.employee_id;

---

Edge Case Spotlight

NULL in Self Joins

-- Employees with no manager
SELECT 
    e.first_name,
    e.manager_id
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE m.employee_id IS NULL AND e.manager_id IS NOT NULL;

This finds orphaned records (manager_id points to non-existent employee).

Circular References

Be careful with self-referencing data:

-- If Alice's manager is Bob, and Bob's manager is Alice (bad data!)
-- This query would show the cycle
SELECT 
    e.first_name AS person,
    m.first_name AS their_manager,
    m2.first_name AS managers_manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
JOIN employees m2 ON m.manager_id = m2.employee_id
WHERE e.employee_id = m2.employee_id;

This detects 2-level circular references.

---

Try This

Exercise 1 (Guided)

Find all pairs of employees in the same department who were hired in the same year. Show both names, department_id, and hire_date. Avoid duplicates.

Hint

Self join on department_id and YEAR(hire_date), use e1.employee_id < e2.employee_id.

Exercise 2 (Independent)

Create a manager report: show each manager’s name, their salary, number of direct reports, and the highest-paid direct report’s salary. Only include managers with at least one report.

Hint

Self join where e.manager_id = m.employee_id, GROUP BY manager, use COUNT and MAX aggregates.

Exercise 3 (Challenge)

Find “salary outliers” — employees whose salary is more than 20% different from the average salary of other employees in their department. Show employee name, their salary, department_id, and department average (excluding them).

Hint

Self join on department_id (excluding self), calculate AVG(e2.salary) as dept_avg, filter with HAVING and percentage calculation.

---

Answer Key

Exercise 1 Answer

```sql SELECT e1.first_name AS employee1, e2.first_name AS employee2, e1.department_id, e1.hire_date AS hire_year FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND YEAR(e1.hire_date) = YEAR(e2.hire_date) AND e1.employee_id < e2.employee_id ORDER BY e1.department_id, e1.hire_date; ``` **Expected output:** | employee1 | employee2 | department_id | hire_year | |-----------|-----------|---------------|-----------| | Bob | Carol | 3 | 2020-05-10 | | Eve | Mia | 3 | 2022-02-14 | Pairs of colleagues hired in the same year.

Exercise 2 Answer

```sql SELECT m.first_name AS manager_name, m.last_name AS manager_lastname, m.salary AS manager_salary, COUNT(e.employee_id) AS direct_reports, MAX(e.salary) AS highest_report_salary FROM employees m JOIN employees e ON m.employee_id = e.manager_id GROUP BY m.employee_id, m.first_name, m.last_name, m.salary ORDER BY direct_reports DESC; ``` **Expected output:** | manager_name | manager_lastname | manager_salary | direct_reports | highest_report_salary | |--------------|------------------|----------------|----------------|-----------------------| | Alice | Johnson | 75000.00 | 3 | 74000.00 | | Bob | Smith | 82000.00 | 2 | 93000.00 | Manager statistics with team insights.

Exercise 3 Answer

```sql SELECT e1.first_name, e1.last_name, e1.salary AS their_salary, e1.department_id, ROUND(AVG(e2.salary), 2) AS dept_avg_excluding_them, ROUND(ABS(e1.salary - AVG(e2.salary)) / AVG(e2.salary) * 100, 2) AS percent_difference FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND e1.employee_id != e2.employee_id GROUP BY e1.employee_id, e1.first_name, e1.last_name, e1.salary, e1.department_id HAVING ABS(e1.salary - AVG(e2.salary)) / AVG(e2.salary) > 0.20 ORDER BY percent_difference DESC; ``` **Expected output:** | first_name | last_name | their_salary | department_id | dept_avg_excluding_them | percent_difference | |------------|-----------|--------------|---------------|-------------------------|-------------------| | Sam | Clark | 95000.00 | 1 | 81000.00 | 17.28 | Employees significantly above/below their department's average. **Note:** This is an advanced query combining self joins, aggregates, and complex HAVING conditions.

---

Quick Recap

• Self join joins a table to itself
• Always use different aliases (e1, e2)
• Use != to avoid self-matches, < to avoid duplicate pairs
• Common uses: hierarchies, comparisons, finding relationships
• LEFT JOIN shows orphaned records (e.g., employees with no manager)
• Beware circular references in hierarchical data
• Can combine with aggregates for powerful analytics

---

Up Next

Next topic: Multiple Joins (3+ Tables) → part2_04_multiple_joins.md

Type ‘next’ when ready to continue!