PostgreSQL Self-Joins

There’s no special syntax for a self-join. You write FROM employees e JOIN employees m, and PostgreSQL treats the two aliases as if they were separate tables that happen to hold identical rows. Everything you know about joins still applies. What changes is that you have to keep two roles for the same rows straight in your head, and the mistakes that come from mixing them up produce results that look plausible.

The queries below ran on SQLite through Python’s sqlite3 module; their behaviour is the same in PostgreSQL. The one PostgreSQL-only statement (DELETE ... USING) is labelled.

Rows that point at rows in the same table

The classic case is a reporting line stored as a foreign key back into the same table:

CREATE TABLE employees (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  manager_id integer REFERENCES employees(id),
  dept       text NOT NULL,
  salary     integer NOT NULL
);

INSERT INTO employees VALUES
  (1, 'Priya',  NULL, 'Exec',  250000),
  (2, 'Marcus', 1,    'Eng',   180000),
  (3, 'Lena',   2,    'Eng',   195000),
  (4, 'Tom',    2,    'Eng',   140000),
  (5, 'Aiko',   1,    'Sales', 160000),
  (6, 'Ravi',   5,    'Sales', 120000),
  (7, 'Sofia',  2,    'Eng',   150000);

To print each person next to their manager’s name, join the table to itself: one copy plays “the employee”, the other plays “the manager”.

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

employee | manager
Marcus   | Priya
Lena     | Marcus
Tom      | Marcus
Aiko     | Priya
Ravi     | Aiko
Sofia    | Marcus

Read the ON clause aloud: “the manager row is the one whose id equals the employee’s manager_id”. If you write m.manager_id = e.id instead, the query still runs and returns six rows, but now m is each person’s direct report and the column labels are lies. This is why short, role-based aliases beat e1 and e2. With e and m, a reversed condition reads wrong; with e1 and e2, both directions look equally reasonable.

Priya is missing from that output. She has no manager, e.manager_id is NULL, and NULL = anything is never true, so the inner join drops her. If the report must list everyone, use a left join and decide what to show:

SELECT e.name AS employee, COALESCE(m.name, '(none)') AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

That returns seven rows with Priya | (none) first. Interviewers like asking “how many rows does this return?” for exactly this reason: the inner join quietly loses the top of the hierarchy.

Once both copies are in one row, comparing them is just a WHERE clause. Who earns more than their own manager?

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

name | salary | manager | manager_salary
Lena | 195000 | Marcus  | 180000

You can chain the pattern one more step (join m to a third alias g on g.id = m.manager_id) to get skip-level managers. Each extra level is another join, though, so if the question becomes “everyone above this person, however many levels”, you want a recursive CTE rather than a stack of aliases.

Pairing rows without the accidental cross product

The second family of self-joins doesn’t follow a foreign key at all. It pairs rows that share a value: “list every pair of engineers who could pair-program”. The obvious query is wrong in an instructive way:

SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.dept = b.dept
WHERE a.dept = 'Eng';

Engineering has four people, and this returns 16 rows. Joining on a non-unique column matches every engineer with every engineer, including themselves. That’s a cross product scoped to the department, and on real data it’s how a harmless-looking report turns a 10,000-row department into 100 million rows.

There are two kinds of junk in those 16 rows. Four are self-pairs (Marcus with Marcus). Twelve remain, but each pair appears twice, as (Marcus, Lena) and (Lena, Marcus). Adding a.id <> b.id removes only the first kind and leaves 12 rows. Adding a.id < b.id removes both, because for any two distinct ids exactly one ordering satisfies <:

SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.dept = b.dept AND a.id < b.id
WHERE a.dept = 'Eng';

name   | name
Marcus | Lena
Marcus | Tom
Marcus | Sofia
Lena   | Tom
Lena   | Sofia
Tom    | Sofia

Six rows, which matches the combinatorics: four choose two is six. Doing that check out loud is a good habit in an interview, since it shows you’re predicting the row count rather than hoping.

The same pairing works on time. To compare each reading with the previous reading for the same sensor, you’d join on sensor_id and an inequality on the timestamp, then keep only the closest earlier row. That’s possible with a self-join plus a NOT EXISTS, but LAG() does it in one pass and is what most reviewers expect in PostgreSQL. Knowing when a window function replaces a self-join is part of the answer.

Finding and removing duplicates

A signup form that didn’t enforce uniqueness has left this behind:

CREATE TABLE customers (id integer PRIMARY KEY, email text, created_at date);

INSERT INTO customers VALUES
  (10, 'ana@example.com', '2026-01-04'),
  (11, 'ben@example.com', '2026-01-09'),
  (12, 'ana@example.com', '2026-03-15'),
  (13, 'ana@example.com', '2026-05-02'),
  (14, 'cy@example.com',  '2026-05-20');

Pairing with b.id > a.id lists each duplicate against every earlier copy:

SELECT a.id AS keep_id, b.id AS dup_id, a.email
FROM customers a
JOIN customers b ON b.email = a.email AND b.id > a.id;

keep_id | dup_id | email
10      | 12     | ana@example.com
10      | 13     | ana@example.com
12      | 13     | ana@example.com

Three rows for three copies of one email. That’s fine for inspection, but it’s a trap if you feed it to something that processes each dup_id once: 13 appears twice. When what you actually need is “every row that has an older twin”, ask the question as a semi-join so each candidate appears once:

SELECT b.id
FROM customers b
WHERE EXISTS (
  SELECT 1 FROM customers a
  WHERE a.email = b.email AND a.id < b.id
);

That returns 12 and 13, which are the rows to delete if you keep the oldest. In PostgreSQL the delete itself is usually written with USING, which is a self-join inside a DELETE:

-- PostgreSQL
DELETE FROM customers b
USING customers a
WHERE a.email = b.email
  AND a.id < b.id;

Before running that against production, check what “duplicate” means. Ana@Example.com and ana@example.com don’t match on =, so you may need lower(a.email) = lower(b.email). Child rows (orders, addresses) that reference the duplicates have to be repointed to the surviving id first, or the foreign keys will block the delete. And the cleanup only sticks if you add the constraint afterwards: a unique index on lower(email) in PostgreSQL.

Performance: it’s two scans of one table

The planner treats the aliases independently, so the usual rules apply. The manager lookup joins on m.id, the primary key, and is cheap. The direct-reports direction (m.manager_id = e.id) and the duplicate search (b.email = a.email) need indexes on manager_id and email, or PostgreSQL will choose a hash join over two full scans, which is fine for thousands of rows and painful for hundreds of millions. EXPLAIN on a self-join shows the table twice with its two aliases, which makes it easy to see which side lacks an index.

Interview exercise: the mentoring report

“HR wants a list of every pair of employees in the same department where one earns at least 20% more than the other, so senior people can mentor junior ones. Each pair should appear once, with the higher earner first. Write it, and tell me how many rows you’d expect for the sample table above.”

Answer. This is a pairing self-join, but the order inside the pair is now meaningful, so the inequality goes on salary instead of id:

SELECT s.name AS mentor, j.name AS mentee, s.dept
FROM employees s
JOIN employees j
  ON j.dept = s.dept
 AND s.salary >= j.salary * 1.2
ORDER BY s.dept, s.name, j.name;

s.salary >= j.salary * 1.2 can’t be true in both directions for positive salaries, so mirrored pairs and self-pairs disappear without an id comparison. Before running it, work out the thresholds: Tom’s is 168,000 and Sofia’s is exactly 180,000, so both Lena (195,000) and Marcus (180,000) clear both of them, while nobody clears Marcus’s 216,000 or Lena’s 234,000. In Sales, Aiko’s 160,000 clears Ravi’s 144,000. Exec has one person and produces nothing. So I’d predict five rows, and that’s what the query returns:

mentor | mentee | dept
Lena   | Sofia  | Eng
Lena   | Tom    | Eng
Marcus | Sofia  | Eng
Marcus | Tom    | Eng
Aiko   | Ravi   | Sales

Marcus/Sofia is the edge case worth pointing out: it’s included only because of >=, since 180,000 is exactly 1.2 times 150,000. I’d confirm with HR whether “at least 20%” is meant inclusively. On types, multiplying an integer by 1.2 promotes to numeric in PostgreSQL, so that boundary comparison is exact rather than a floating-point near miss. Finally, on a large table I’d want an index on dept, because the join condition is an equality on department plus a range filter on salary, and every department is compared against itself.

Further reading

• SQL interview questions
• SQL quiz questions
• Join cardinality and row multiplication
• EXISTS versus JOIN
• PostgreSQL tutorial: joins between tables