SQL Self Joins for Data Analysts

Most joins connect two different tables. But sometimes the relationship you need to report on lives inside a single table. An employees table where each row has a manager_id pointing at another employee. A table of orders where you want to compare each order to the same customer's previous one. In these cases you join the table to itself — a self join. It confuses beginners because the two "tables" are physically the same, but once you see it as two logical copies with two names, it becomes routine.

This tutorial follows joins explained; make sure INNER and LEFT joins are solid first. It sits in the intermediate stretch of the SQL for analytics path.

A table that references itself

The classic self-join dataset is an employee table where managers are also employees:

-- employees
emp_id | emp_name | manager_id | department
-------+----------+------------+-----------
1      | Aarti    | NULL       | Sales
2      | Bhaskar  | 1          | Sales
3      | Chitra   | 1          | Sales
4      | Devan    | 2          | Sales
5      | Esha     | 2          | Sales

manager_id refers back to an emp_id in the same table. Aarti has no manager (she is at the top). Bhaskar and Chitra report to Aarti. Devan and Esha report to Bhaskar. To produce a report of "employee and their manager's name", you must look up each manager_id in the same table.

Writing the self join

Give the table two aliases — e for the employee, m for the manager — and join them:

SELECT e.emp_name  AS employee,
       m.emp_name  AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.emp_id;

employee | manager
---------+--------
Aarti    | NULL
Bhaskar  | Aarti
Chitra   | Aarti
Devan    | Bhaskar
Esha     | Bhaskar

Read the ON clause carefully: each employee row's manager_id is matched to a manager row's emp_id. The two aliases let SQL treat the one table as two, so e.emp_name and m.emp_name come from different "copies". The LEFT JOIN keeps Aarti even though her manager_id is NULL — an INNER JOIN would silently drop the top of the hierarchy, which is a common bug in org-chart reports.

Counting reports per manager

Combine a self join with grouping to answer "how many people does each manager have?":

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

manager | direct_reports
--------+---------------
Aarti   | 2
Bhaskar | 2

Here an INNER JOIN is correct because we only want people who actually manage someone. This kind of "supervisor span" report is a genuine HR-analytics deliverable.

Comparing rows within one table

Self joins also compare rows to each other. Suppose you want pairs of employees in the same department earning within a comparison — for example, listing every pair of colleagues in the same department:

SELECT a.emp_name AS employee_1,
       b.emp_name AS employee_2,
       a.department
FROM employees a
JOIN employees b
    ON a.department = b.department
   AND a.emp_id < b.emp_id;

The condition a.emp_id < b.emp_id is the trick that prevents an employee from pairing with themselves and stops each pair appearing twice (Aarti-Bhaskar and Bhaskar-Aarti). This "less-than on the key" pattern appears whenever you generate unique pairings from a single table.

Practical usage

In analytics, self joins show up most in three situations: resolving hierarchies (employee-manager, category-parent category), building pairwise comparisons (customers in the same city, products in the same price band), and simple duplicate detection. For hierarchies that go several levels deep — manager of manager of manager — a single self join is not enough, and you graduate to a recursive CTE. But for one level of relationship, a self join is the cleanest tool.

Duplicate detection is worth a concrete look, because it is a routine data-quality task. Suppose you suspect the same customer was entered twice under different IDs but with an identical email. A self join surfaces the offending pairs:

SELECT a.emp_id AS id_1, b.emp_id AS id_2, a.emp_name
FROM employees a
JOIN employees b
    ON a.emp_name = b.emp_name
   AND a.emp_id < b.emp_id;

Again the a.emp_id < b.emp_id inequality keeps each duplicate pair once. For pure "how many duplicates exist" counting, GROUP BY emp_name HAVING COUNT(*) > 1 is simpler, but the self join has the advantage of showing both conflicting IDs side by side so you can decide which record to keep.

Common mistakes

• Forgetting distinct aliases. Referencing the table name twice without aliases makes every column ambiguous. Always give the two copies different names.
• Using INNER JOIN and losing the top of a hierarchy. Top-level rows have a NULL parent key; an INNER self join drops them. Use LEFT JOIN when the report must include them.
• Producing duplicate or self pairs. Comparing rows without an inequality like a.id < b.id yields self-matches and mirror-image duplicates. Add the inequality to keep pairs unique.
• Assuming one join covers deep hierarchies. A self join reaches exactly one level up or down. Multi-level trees need recursion, not repeated joins.

In interviews

The employee-manager self join is one of the most-asked analyst SQL questions: "list each employee with their manager's name" or "find employees who earn more than their manager". The latter compares two aliases on salary and is a reliable filter for whether a candidate truly understands that the two aliases are independent copies. Expect a follow-up on why you chose LEFT versus INNER, and on how you would handle a hierarchy deeper than one level, which opens the door to CTEs.

Where this fits in your learning path

Self joins extend joins explained to relationships inside a single table. When comparisons get more complex — "employees above the department average" — you often reach for subqueries instead, and for multi-level hierarchies you move to CTEs. All are staples of the data analyst roadmap.