Departments With No Employees

Mediumsql

Write an anti-join query returning id, name for every department that has NO employees, ordered by id.

sql
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  manager_id INTEGER,
  salary INTEGER NOT NULL
);

Sample data:

sql
INSERT INTO departments (id, name) VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing');
INSERT INTO employees (id, name, department_id, manager_id, salary) VALUES
(1, 'Asha', 1, NULL, 90000),
(2, 'Rohan', 1, 1, 70000),
(3, 'Neha', 2, 1, 65000),
(4, 'Vikram', NULL, 1, 50000);

Example 1

Input

(none)

Output

id	name
3	Marketing

LEFT JOIN then filter to rows where the employee side is NULL. Reference: SELECT d.id, d.name FROM departments d LEFT JOIN employees e ON e.department_id = d.id WHERE e.id IS NULL ORDER BY d.id;

Departments With No Employees

Related Problems

Input (stdin)

Output