Difference between count(1) and count(*)

Asked By 100 points N/A Posted on -
qa-featured

Hi,

I am new to Oracle. I have a doubt regarding two basic queries I see everyday at work. Say, I have a table employee with 1000 records. When I fire:

 

Select count(*) from employee;

 

And

 

Select count(1) from employee;

 

They both give me the same result – 1000.

Is there any difference between count(*) and count(1)?

SHARE
Best Answer by Ricky_M
Best Answer
Best Answer
Answered By 0 points N/A #89123

Difference between count(1) and count(*)

qa-featured

Hello Justin,

This is a very interesting question. People tend to believe that count(1) is faster than count(*) but that is not the case. Both are the same as count(1) as it is mapped to count(*) internally.
 
Count(*) is used to count the number of records as you have seen in your query output example. count(1) because is mapped to count(*) will also do the same.
 
Count(1) signify counting of records that are Not Null occurrences of the expression in the parenthesis. In this case it is 1 which is non-null.
Answered By 100 points N/A #89124

Difference between count(1) and count(*)

qa-featured

 

I didn't know about the internal mapping concept. So when you say Not Null occurrences of the expression in the parenthesis, if I do
 
Select count(employee_id) from employee;
 
Will it work same as count(1)?
(Where employee_id is one of the columns in the table employee)
Answered By 0 points N/A #89125

Difference between count(1) and count(*)

qa-featured

No. The result won't be the same. count(1) contains a constant in the parenthesis. So if you substitute 1 with any constant you will get the same result. But in this case, you will get the count of employee_id column in the table. So if you have 1000 employee_id stored in your table, then the output of your query :

Select count(employee_id) from employee;
 
Output : 1000
Answered By 100 points N/A #89126

Difference between count(1) and count(*)

qa-featured

Thanks Ricky_M. This clears my doubt.

Just another question based on count(*). Say I want to know about the number of employees in a department what should be my query for that?
Answered By 0 points N/A #89127

Difference between count(1) and count(*)

qa-featured

You can use the following query:

Select count(employee_id),dept_id
From employee
Group by dept_id;
 
This will give you a break up of the number of employees (found using the count function) divided amongst different departments (achieved using the group by clause).

Related Questions