SQLite Self Join

A self join joins a table to itself. The same table appears on both sides of the JOIN clause under different aliases. Self joins are used when rows in a table have a relationship with other rows in the same table — such as an employee who reports to another employee in the same employees table.

The Org Chart Use Case

employees
──────────────────────────────────────────
id │ name    │ manager_id
───┼─────────┼───────────
1  │ CEO     │ NULL      ← no manager (top)
2  │ Alice   │ 1         ← reports to CEO
3  │ Bob     │ 1         ← reports to CEO
4  │ Carol   │ 2         ← reports to Alice
5  │ Dan     │ 2         ← reports to Alice
6  │ Eve     │ 3         ← reports to Bob

HIERARCHY:
CEO (1)
├── Alice (2)
│   ├── Carol (4)
│   └── Dan (5)
└── Bob (3)
    └── Eve (6)

Self Join Syntax

SELECT a.col, b.col
FROM table_name a
JOIN table_name b ON a.some_col = b.other_col;

-- The same table used twice with different aliases (a and b).

Show Each Employee with Their Manager

SELECT e.name   AS employee,
       m.name   AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;

employee │ manager
─────────┼─────────
CEO      │ NULL       ← LEFT JOIN keeps CEO even with no manager
Alice    │ CEO
Bob      │ CEO
Carol    │ Alice
Dan      │ Alice
Eve      │ Bob

Using INNER JOIN would exclude the CEO row since the CEO has no manager. LEFT JOIN keeps all employees.

Show Manager's Team Members

-- Show each manager and their direct reports
SELECT m.name AS manager,
       e.name AS direct_report
FROM employees e
JOIN employees m ON e.manager_id = m.id
ORDER BY m.name;

manager │ direct_report
────────┼──────────────
Alice   │ Carol
Alice   │ Dan
Bob     │ Eve
CEO     │ Alice
CEO     │ Bob

Find Employees at the Same Level

-- Find pairs of employees sharing the same manager
SELECT a.name AS emp1,
       b.name AS emp2,
       m.name AS shared_manager
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                 AND a.id < b.id   ← avoid duplicate pairs
JOIN employees m ON a.manager_id = m.id;

emp1  │ emp2 │ shared_manager
──────┼──────┼──────────────
Alice │ Bob  │ CEO
Carol │ Dan  │ Alice

Another Use Case: Comparing Rows in Same Table

products
──────────────────────────────────
id │ name      │ category │ price
───┼───────────┼──────────┼───────
1  │ Notebook  │ Office   │ 5.00
2  │ Binder    │ Office   │ 8.00
3  │ Pen Pack  │ Office   │ 3.00
4  │ Stapler   │ Office   │ 12.00

-- Find all pairs where one product costs more than another
-- in the same category
SELECT a.name AS product_a,
       b.name AS product_b,
       a.price - b.price AS price_diff
FROM products a
JOIN products b ON a.category = b.category
               AND a.price > b.price
               AND a.id != b.id
ORDER BY price_diff DESC;

product_a │ product_b │ price_diff
──────────┼───────────┼───────────
Stapler   │ Pen Pack  │ 9.00
Stapler   │ Notebook  │ 7.00
Stapler   │ Binder    │ 4.00
Binder    │ Pen Pack  │ 5.00
Binder    │ Notebook  │ 3.00
Notebook  │ Pen Pack  │ 2.00

Self Join vs Subquery

TASK: Find employees managed by Alice

SELF JOIN approach:
──────────────────────────────────────────────────
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE m.name = 'Alice';

SUBQUERY approach:
──────────────────────────────────────────────────
SELECT name FROM employees
WHERE manager_id = (
  SELECT id FROM employees WHERE name = 'Alice'
);

Both produce the same result. Self join is more readable when
you need multiple columns from the manager record alongside
the employee record.

Key Takeaways

• A self join joins a table to itself using two different aliases
• Use LEFT JOIN in self joins when some rows may have no matching row (like a top-level manager with no boss)
• Common uses: hierarchies (org charts, categories), finding pairs, comparing rows
• Use a.id < b.id to prevent returning symmetric duplicates when pairing rows
• Any JOIN type (INNER, LEFT, CROSS) can be used as a self join