Departments With At Least One Employee

Mediumsql

Write a semi-join query returning id, name for every department that has at least one employee — each department appearing exactly once regardless of how many employees it has. Order 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
1	Engineering
2	Sales

EXISTS checks for a match without duplicating rows or pulling in columns from the matched table. Reference: SELECT d.id, d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id) ORDER BY d.id;

Departments With At Least One Employee

Related Problems

Input (stdin)

Output