The difference between COUNT(*) AND COUNT([attribute]) in the following scenario

countgroup by

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?

Best Answer

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

Related Question