🔗 Joinsintermediate

How to do a self-join in SQL

Q: Find pairs of employees in the same country

The a.id < b.id condition prevents duplicate pairs (A,B) and (B,A) and self-matches.

Join a table to itself to compare rows or navigate hierarchies.

Table schema used in examples— An employees table with a manager_id self-reference

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

List employees with their managerAll databases

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

Joins the employees table to itself. Each employee row is joined to the row representing their manager.

When to use: Querying hierarchical data like org charts, category trees, comment threads.

Find employees without a manager (top level)All databases

SELECT name, role
FROM employees
WHERE manager_id IS NULL;

Top-level employees have no manager — their manager_id is NULL.

Find pairs of employees in the same countryAll databases

SELECT
  a.name AS employee_1,
  b.name AS employee_2,
  a.country
FROM employees a
INNER JOIN employees b
  ON a.country = b.country
  AND a.id < b.id;

The a.id < b.id condition prevents duplicate pairs (A,B) and (B,A) and self-matches.

When to use: Finding matching pairs, duplicate detection.

Common Mistakes

→Not using different aliases for each instance of the table
→Forgetting the a.id < b.id condition when finding pairs — produces duplicates

Common Mistakes

Related Topics