Self Join in SQL: A Step-by-Step Guide

Join a table to itself to compare rows within the same dataset.

A self join is simply a regular join where a table is joined to itself. It looks unusual the first time you see it, but it solves a very common problem: comparing rows within the same table.

Step 1: Identify the Relationship

Self joins are used when one row in a table references another row in the same table — the classic example is an employees table where each employee has a manager_id that points to another employee's id.

Step 2: Alias the Table Twice

Since you're joining a table to itself, you need two different aliases to distinguish the two "copies."

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

Step 3: Choose the Right Join Type

Use LEFT JOIN instead of JOIN if you also want to see employees who have no manager assigned (like the CEO):

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

Step 4: Add Filters if Needed

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE m.department = 'Engineering';

💡 Tip: If your query returns unexpectedly large results, double check your join condition — a missing or incorrect condition can accidentally produce a cross join.

Common Use Cases