SQL SELF JOIN

A self join joins a table to itself. You give the table two aliases and treat them as if they were two separate tables. It's how you answer questions that compare rows within one table, like "who is each employee's manager?"

Syntax

SELECT a.column, b.column
FROM table_name a
JOIN table_name b
  ON a.some_column = b.other_column;

There's no special keyword: it's an ordinary JOIN (or LEFT JOIN) where both sides are the same table.

Examples

1. Employees and their managers

The employees table stores each person's manager_id, which is another employee's employee_id:

SELECT e.employee_name AS employee,
       e.department,
       m.employee_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.employee_name, e.employee_name;

Result:

(11 rows)

Read e as "the employee row" and m as "the manager row". The CEO, Rajesh Khanna, is missing because his manager_id is NULL.

2. Keep the top of the hierarchy with LEFT JOIN

SELECT e.employee_name AS employee,
       COALESCE(m.employee_name, '— (top of org)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id
LIMIT 5;

Result:

(5 rows)

3. Employees who earn more than their manager

A classic interview question:

SELECT e.employee_name, e.salary, m.employee_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Result:

(0 rows)

Nobody at ShopKart does. A softer version: employees earning more than 55% of their manager's pay.

SELECT e.employee_name,
       e.salary,
       m.employee_name AS manager,
       m.salary AS manager_salary,
       ROUND(e.salary / m.salary * 100) AS pct_of_manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > 0.55 * m.salary;

Result:

(3 rows)

4. Team size per manager

SELECT m.employee_name AS manager,
       COUNT(e.employee_id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.employee_id
GROUP BY m.employee_name
ORDER BY direct_reports DESC, manager;

Result:

(6 rows)

5. Pairs within the same group

Pairs of customers who live in the same city, each pair shown once:

SELECT a.customer_name AS customer_1,
       b.customer_name AS customer_2,
       a.city
FROM customers a
JOIN customers b
  ON a.city = b.city
 AND a.customer_id < b.customer_id;

Result:

(1 row)

a.customer_id < b.customer_id stops a customer pairing with themselves and avoids listing both (A, B) and (B, A).

Common mistakes

• Using the same alias twice, or no aliases. Each copy of the table needs its own name.
• Mixing up direction. ON e.manager_id = m.employee_id finds each employee's manager. Swapping the columns finds each manager's reports, so name aliases clearly.
• Using <> instead of < for pairs, which returns every pair twice.

Practice question

Find pairs of employees in the same department who earn exactly the same salary. Show each pair once.

SELECT a.employee_name AS employee_1,
       b.employee_name AS employee_2,
       a.department,
       a.salary
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.salary = b.salary
 AND a.employee_id < b.employee_id;

Key takeaways

• A self join is a normal join with the same table on both sides.
• Two aliases (such as e and m) make the roles clear.
• Use LEFT JOIN to keep rows with no match, like the top of a hierarchy.
• a.id < b.id returns each pair once. For multi-level hierarchies, use a recursive CTE.