Asked By
justin
100 points
N/A
Posted on - 04/28/2011
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)?
Answered By
Ricky_M
0 points
N/A
#89123
Difference between count(1) and count(*)
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
justin
100 points
N/A
#89124
Difference between count(1) and count(*)
Â
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
Ricky_M
0 points
N/A
#89125
Difference between count(1) and count(*)
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
justin
100 points
N/A
#89126
Difference between count(1) and count(*)
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
Ricky_M
0 points
N/A
#89127
Difference between count(1) and count(*)
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).
