Search code examples
mysqlsqlsubqueryleft-joinscalar-subquery

Left outer join vs subquery to include departments with no employees

Lets say I have the following database model:

enter image description here

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?

Solution

  • 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-*.)