Find unique pairs of department coworkers
Return all pairs of employees sharing a non-NULL department. Output the smaller employee ID first and never include a self-pair or reversed duplicate. Return only the requested columns and follow the specified ordering. This question probes an inequality self join generates each unordered pair exactly once; output size can be quadratic.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
department_id, employee_one, employee_two; ascending all three columns.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Ana',10,NULL,100),(2,'Ben',10,1,200),(3,'Cara',20,1,200),(4,'Dan',20,3,300),(5,'Eve',NULL,NULL,NULL);
Expected result
department_id | employee_one | employee_two 10 | 1 | 2 20 | 3 | 4
Another dataset (1)
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Solo',10,NULL,100),(2,'Twin',10,1,100),(3,'Null',NULL,NULL,NULL);
Expected result
department_id | employee_one | employee_two 10 | 1 | 2
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Match department IDs.
- Use a.id < b.id instead of a.id <> b.id.
Target complexity: time O(n^2) · space O(n^2)