Self Joins & Hierarchical Queries SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Find Pairs of Employees in the Same Department

Find pairs of employees who work in the same department. Exclude self-pairs and avoid duplicate (A,B)/(B,A) pairs by enforcing employee1_id < employee2_id. Skip rows where department_id IS NULL. Return employee1_name, employee2_name, department_id — ordered by department_id, employee1_name, employee2_name.

  • Joins
  • NULL handling
  • Filtering
  • Sorting

Exercise brief

Understand the request

Workforce collaboration analyst A peer-matching export needs unique pairs within each department without mirrored or self-pairs.

Return

  • Return both names and department_id.
  • Order by department and both names.

Constraints

  • Self-join on department_id.
  • Enforce employee1_id < employee2_id.
  • Exclude NULL department keys.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • employee_nameTEXT
  • department_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Pair-finding self-join: ON shared_attr = shared_attr AND id1 < id2.

Hint 2

The strict-less-than (<) does double duty: drops self-pairs and dedupes (A,B)/(B,A).

Hint 3

NULL-safe: filter `department_id IS NOT NULL` because NULL = NULL is unknown.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e1.employee_name AS employee1_name, e2.employee_name AS employee2_name, e1.department_id FROM employees e1 INNER JOIN employees e2 ON e1.department_id = e2.department_id AND e1.employee_id < e2.employee_id WHERE e1.department_id IS NOT NULL ORDER BY e1.department_id, e1.employee_name, e2.employee_name;

Why this works

The id1 < id2 trick is the canonical way to enumerate unordered pairs in SQL. Without it you would get N×N rows including self-pairs and mirror duplicates.

Success check

Every unordered same-department pair appears once and no employee is paired with themself.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee1_nameemployee2_namedepartment_id
Bob VP SalesEmma Sales Mgr2
Bob VP SalesHenry Sales Rep2
Bob VP SalesIvy Sales Rep2
Bob VP SalesMia Overpaid Jr2
Bob VP SalesOlivia Sales Rep2
Emma Sales MgrHenry Sales Rep2
Emma Sales MgrIvy Sales Rep2
Emma Sales MgrMia Overpaid Jr2
Emma Sales MgrOlivia Sales Rep2
Henry Sales RepIvy Sales Rep2

Previewing 10 of 28 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.