✏️ Explanatory Question
Level: Coding Round — Tests the self-join "pairing" pattern and the classic trick to avoid duplicate and self-matched pairs.
The Puzzle: Given an employees table, list all pairs of employees who work in the same department. Each pair should appear only once (do not show both "A-B" and "B-A"), and an employee must not be paired with themselves.
employees| id | name | dept_id |
|---|---|---|
| 1 | Rumman | 10 |
| 2 | Krushna | 10 |
| 3 | Swetha | 10 |
| 4 | Ritesh | 20 |
| employee_1 | employee_2 | dept_id |
|---|---|---|
| Rumman | Krushna | 10 |
| Rumman | Swetha | 10 |
| Krushna | Swetha | 10 |
Dept 10 has 3 people → 3 unique pairs. Ritesh is alone in dept 20 → no pair. Note "Krushna-Rumman" does NOT appear separately.
The essential trick — a.id < b.id: A plain self-join on dept_id would produce (1) self-pairs like "Rumman-Rumman" and (2) mirror duplicates like both "A-B" and "B-A". Adding a.id < b.id keeps only one ordering of each pair and eliminates self-matches in a single condition.
SELECT
a.name AS employee_1,
b.name AS employee_2,
a.dept_id
FROM employees a
JOIN employees b
ON a.dept_id = b.dept_id -- same department
AND a.id < b.id -- avoids self-pairs AND mirror duplicates
ORDER BY a.dept_id, a.name, b.name;| Join Condition | Result |
|---|---|
a.dept_id = b.dept_id only | Includes self-pairs + both A-B and B-A |
Add a.id != b.id | Removes self-pairs, but still has mirrors |
Add a.id < b.id | Clean: no self-pairs, no mirrors |
The pair-count math: A department with n employees produces n × (n-1) / 2 unique pairs. Dept 10 has 3 people → 3 × 2 / 2 = 3 pairs, matching our output. This is the "n choose 2" combination formula — handy to mention.
SELECT a.dept_id, COUNT(*) AS pair_count
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id AND a.id < b.id
GROUP BY a.dept_id;
-- Dept 10 -> 3 pairs, Dept 20 -> 0 (no row, single employee) Interviewer follow-up: "Why use a.id < b.id instead of a.name < b.name?" → Use the id (or any unique key) because names can be duplicated — two employees named "Rumman" would break a name-based comparison (they would be wrongly filtered or paired). Ordering on a guaranteed-unique column like the primary key is always the safe choice for this de-duplication trick.