Left outer join vs subquery to include departments with no employees

Question

Lets say I have the following database model:

And the question is as follows:

List ALL department names and the total number of employees in the department. The total number of employees column should be renamed as "total_emps". Order the list from the department with the least number of employees to the most number of employees. Note: You need to include a department in the list even when the department does not currently have any employee assigned to it.

This was my attempt:

SELECT Department.deptname

   (SELECT COUNT(*)
   FROM Department
   WHERE Department.empno = Employee.empno ) AS total_emps

FROM Department

I'm pretty sure my solution is not correct as it won't include departments with no employees. How do you use a left inner join to solve this problem?

Answer 1

First off, it's a left outer join. Now, for your query, you want to join the 2 tables based on deptno, then also group by deptno (or deptname, since that is as likely to be unique) to ensure that any aggregation we do is done for each unique department in the table. Finally, the counting is done with the count function, leading to this query:

select d.deptname, count(e.empno) as total_emps
from department d 
left join employee e on d.deptno = e.deptno
group by d.deptname

SQLFiddle

Note that since we want all records from department regardless of whether there are matching records in employee or not, department must appear at the left side of the join. We could have done the same thing using a right outer join by swapping the positions of the 2 tables in the join.

Answer 2

The query as you were trying to write it is:
(table creates modified from shree.pat18's sqlfiddle to this sqlfiddle)

create table department (deptno int, deptname varchar(20));
insert into department values (1, 'a'),(2, 'b'),(3, 'c');
create table employee (empno int, deptno int);
insert into employee values (1,1),(2,1),(3,3);

SELECT d.deptname, 
    (SELECT COUNT(*)
    FROM EMPLOYEE e
    WHERE d.deptno = e.deptno ) AS total_emps
FROM DEPARTMENT d
ORDER BY total_emps ASC;

(You were counting from DEPARTMENT instead of EMPLOYEE and comparing empno instead of deptno. And you left out a comma.)

(You were asked for every department's name and employee count so this returns that. In practice we would include a presumably unique deptno if deptname was not unique.)

I'm pretty sure my solution is not correct as it won't include departments with no employees.

Even your answer's version of the query (with the missing comma added) has an outer select that returns a count for every department no matter what the subselect returns. So I don't know why/how you thought it wouldn't.

If you want to use LEFT (OUTER) JOIN then the DEPARTMENT rows with no employees get extended by NULL. But COUNT of a column only counts non-NULL rows.

SELECT d.deptname, COUNT(e.empno) AS total_emps
FROM DEPARTMENT d
LEFT JOIN EMPLOYEE e
ON d.deptno = e.deptno
GROUP BY d.deptno
ORDER BY total_emps ASC;

(Nb the LEFT JOIN version uses more concepts: LEFT JOIN extending by NULL, GROUP BY, and COUNT's NULL behaviour for non-*.)