Skip to main content
Question

You are given two tables: Employee and Department. Each employee is associated with a department through the dept_id column.

Write an SQL query to retrieve the number of employees in each department.Ensure that the result includes all departments, even those that currently have no employees.

Answer

Assuming Tables:

Department Table

| dept_id | department_name |
| ------- | --------------- |
| 1 | HR |
| 2 | IT |
| 3 | Finance |
| 4 | Marketing |

Employee Table

| employee_id | employee_name | dept_id |
| ----------- | ------------- | ------- |
| 101 | John | 1 |
| 102 | Alice | 2 |
| 103 | Bob | 2 |
| 104 | David | 3 |
| 105 | Emma | 2 |


Join Department to Employee with a LEFT JOIN, then group by department and count employee IDs.

SELECT
d.dept_id,
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM Department d
LEFT JOIN Employee e
ON d.dept_id = e.dept_id
GROUP BY
d.dept_id,
d.department_name
ORDER BY d.dept_id;


Expected output

dept_id

department_name

employee_count

1

HR

1

2

IT

3

3

Finance

1

4

Marketing

0

How it works

  1. The LEFT JOIN keeps every row from Department and matches employees on dept_id. A department with no employees still gets a row, with NULL in all the Employee columns.
  2. COUNT(e.employee_id) counts the employees in each department. It skips NULL values, so it returns 0 for a department with no employees.
  3. GROUP BY on dept_id and department_name gives one row per department, assuming dept_id is unique in Department.
  4. ORDER BY sorts the result by department ID.


Follow-up questions

Why use COUNT(e.employee_id) instead of COUNT(*)?

When a department has no employees, the LEFT JOIN still produces one row for it, with NULL in the Employee columns. COUNT(*) counts that row. COUNT(e.employee_id) counts only non-NULL employee IDs, so it skips it.

Marketing has no employees, so COUNT(*) returns 1, which is wrong. COUNT(e.employee_id) returns 0.


What happens if you use INNER JOIN instead?

An INNER JOIN returns only departments that have at least one matching employee, so Marketing drops out of the result.

Run the SQL yourself

These are the tables from the answer. Write a query, or load one of the examples, and run it.

Running…

Running…

Examples:

Runs in your browser on a fresh copy of the tables each time, so nothing you change is kept. The engine is SQLite: FULL OUTER JOIN runs here, though MySQL and MariaDB (Appian Cloud) do not have it.

JuniorSeniorDatabase#secenrio_based