I have a problem about writing a SQL query on counting the number of employees.
Here is the question: There are 2 tables,
T1, the attributes are
E_ID(primary key), E_Name, DpartmentID;
DepartmentID(primary key), DepartmentName
Mow i want to count the number of employees in technology department, should i write as below?
FROM T1 JOIN T2 ON T1.DepartmentID = T2.DepartmentID
WHERE T2.DepartmentName = 'Technology'
I was asked this question during an interview, but the interviewee said I missed something... can anyone help me with it? thanks!
Your query makes some assumptions:
I would write that query as
select dept.DepartmentId as department_id , sum(case when emp.E_ID is not null then 1 else 0 end) as employee_count , from T2 dept left join T1 emp on emp.DepartmentId = dept.DepartmentId where dept.DepartmentName = 'Technology' group by t2.DepartmentId
You'll notice that I've inverted the join order to emphasize the fact that you're producing summary information about the department.
We do a left [outer] join from the department table to the employee table. This guarantees we get at least one row for every department, even if there are no employees assigned to it.
Then we use
sum() with a *discriminant function to product the count.
Another approach you could take might be
You should try
select dept.DepartmentId as department_id , ( select count(*) from T1 emp where emp.DepartmentId = dept.DepartmentId ) as employee_count from T2 dept where dept.DepartmentName = 'Technology' group by dept.DepartmentId
Whether this gets a better execution plan than my first solution is dependent on all sorts of factors in your database. The only way to tell would be to check the execution plan against production-like data.
Where this query is simpler, more (or less) elegant than my first solution is subjective. This solution does contain a correlated subquery which [theoretically] means that the subquery has to be executed once for every row in the outer result set — which is presumably 1, so it should be wash from an execution plan perspective. Of course, any decent query optimizer out to be able to refactor it into a join anyway ... but I digress.