Find departments with no employees
Return department ID and name for departments having no matching employee. Return only the requested columns and follow the specified ordering. This question probes a left anti-join preserves departments without matches.
Input
Setup script defining departments(id INT, name VARCHAR(30)); employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
id, name; ascending department ID.
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 departments(id INT, name VARCHAR(30)); INSERT INTO departments VALUES (10,'QA'),(20,'Dev'),(30,'Ops'); 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
id | name 30 | Ops
Another dataset (1)
CREATE TABLE departments(id INT, name VARCHAR(30)); INSERT INTO departments VALUES (10,'QA'),(40,'Empty'); 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
id | name 40 | Empty
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Start from departments with a LEFT JOIN.
- Test the non-nullable employee ID for NULL.
Target complexity: time O(n) · space O(n)