SQL self join

Join a table to itself to compare rows within the same table, such as employees and their managers.

A self join is when a table is joined to itself. This is useful for hierarchical or comparative data within the same table.

A small organization chart with Sarah at the top, John and Jane under her, and Bob under John, each box showing id and manager_id — An arrow points from an employee to their manager. A self join matches employees.manager_id to employees.id.

Example: Employee Hierarchy

employees

id	name	manager_id
1	Sarah	NULL
2	John	1
3	Jane	1
4	Bob	2

Create the Sample Table

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
  (1, 'Sarah', NULL),
  (2, 'John', 1),
  (3, 'Jane', 1),
  (4, 'Bob', 2);

Finding Managers

-- Get each employee with their manager's name
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
-- Sarah | NULL   (CEO has no manager)
-- John  | Sarah
-- Jane  | Sarah
-- Bob   | John

Finding Direct Reports

-- Count direct reports for each manager
SELECT 
    m.name AS manager,
    COUNT(e.id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name
HAVING COUNT(e.id) > 0;

-- Result:
-- Sarah | 2
-- John  | 1

Comparing Within a Table

Create a small products table, then list every product that costs more than the Basic Plan.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Basic Plan', 10),
  (2, 'Pro Plan', 25),
  (3, 'Team Plan', 60);

SELECT p1.name AS product, p1.price, p2.name AS cheaper_than
FROM products p1
JOIN products p2 ON p1.price > p2.price
WHERE p2.name = 'Basic Plan'
ORDER BY p1.price;

 product   price  cheaper_than
---------  -----  ------------
Pro Plan      25  Basic Plan
Team Plan     60  Basic Plan

Important: Always use different aliases (e, m) when self-joining to distinguish between the two "copies" of the table.