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.
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
Employee Table
Join Department to Employee with a LEFT JOIN, then group by department and count employee IDs.
Expected output
dept_id | department_name | employee_count |
|---|---|---|
1 | HR | 1 |
2 | IT | 3 |
3 | Finance | 1 |
4 | Marketing | 0 |
How it works
- The
LEFT JOINkeeps every row fromDepartmentand matches employees ondept_id. A department with no employees still gets a row, withNULLin all the Employee columns. COUNT(e.employee_id)counts the employees in each department. It skipsNULLvalues, so it returns0for a department with no employees.GROUP BYondept_idanddepartment_namegives one row per department, assumingdept_idis unique inDepartment.ORDER BYsorts 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…
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.