Difference between count(1) and count(*)

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)?

• Status: Open
• Question Views: 1603
• Answer Count: 5
• Vote Up 10 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Ricky_M

Go To Solution

Best Answer

Best Answer

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.

About Ricky_M

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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)

About justin

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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

About Ricky_M

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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?

About justin

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

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).

About Ricky_M

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database