What is the difference between COUNT(*) AND COUNT([attribute]) in the following scenario? I have the following tables :


DEPARTMENT

department_id
department_name
manager_id
location_id


and


EMPLOYEES

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id


I want to count the number of employees working in each department.

I have used the following query :

SELECT department_name, count(*)
FROM departments d LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name;


But in the results the employee which has no employees does not show up in the results. I want the number of employees to show up as 0. How do I do that?
In terms of the speed:


As I know that both the queries (select count(*) or Select Count(id)) are using same execution plan, so should use same amount of CPU as long as blocks are fetched from either buffer pool or from disk not mix.


  In terms of the result, and in your case:



if you said count(*), then it will count the row even if you don't have employees since you are making left join.
if you said count(employee_id), then this will only count the rows "which are exists", means not the null rows.. I think this is what you need to have 0 for your case
If you want to mention the field and you want to count it even if there's no row you have to say count(NVL(employee_id, 123)) while 123 is any value in case null