SQL Self Join Explained

A self join joins a table to itself, treating it as two copies with different aliases. It's the standard way to relate rows within one table — an employee to their manager, or one row to its duplicate.

How Do You Join a Table to Itself?

List the table twice with two aliases and join them like any other pair of tables. The aliases are mandatory — without them the database can't tell which copy a column refers to.

-- employees: id | name  | manager_id
--            1  | Dana  | NULL
--            2  | Ben   | 1
--            3  | Chris | 1
--            4  | Ana   | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;
-- employee | manager
-- Ben      | Dana
-- Chris    | Dana
-- Ana      | Ben

Mechanically this is a plain inner join; the only twist is that both sides are the same table.

How Do You Include Employees With No Manager?

Use a LEFT JOIN so rows without a match (the CEO) survive with a NULL manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- employee | manager
-- Dana     | NULL      <- kept by the LEFT JOIN
-- Ben      | Dana
-- ...

How Do You Find Duplicate Pairs With a Self Join?

Join the table to itself on the columns that define "duplicate," and use < on the primary key so each pair appears once instead of twice (and rows don't match themselves).

SELECT a.id, b.id, a.email
FROM users a
JOIN users b
  ON a.email = b.email
 AND a.id < b.id;        -- not <>, or you get each pair twice
-- id | id | email
-- 7  | 42 | [email protected]

For counting duplicates rather than pairing them, GROUP BY is simpler — see counting duplicates in SQL.

Common Pitfalls

• Missing aliases: FROM employees JOIN employees without aliases is an error (or ambiguous) in every database. Always alias both copies.
• Using <> for pairs: returns each duplicate pair twice (A,B and B,A). Use a.id < b.id.
• One level only: a self join gets manager, and joining again gets grandmanager, but arbitrary depth needs a recursive CTE.
• Selecting bare column names: SELECT name is ambiguous; qualify everything as e.name or m.name.