Home » SQL & PL/SQL » SQL & PL/SQL » Diff between count(1) and count(*)
Diff between count(1) and count(*) [message #201246] Fri, 03 November 2006 04:54 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
Can anyone explain me the diff. between count(1) and count(*) ?
I am using Oracle 9i.

Thanks,
Vishal
Re: Diff between count(1) and count(*) [message #201251 is a reply to message #201246] Fri, 03 November 2006 05:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no difference between them.
Re: Diff between count(1) and count(*) [message #201252 is a reply to message #201246] Fri, 03 November 2006 05:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Abolutely none whatsoever.

There used to be some optimizer differences in Oracle 6 and earlier, it seems.

But now count(1)is internally rewriten to count(*) anyway.

There are some religous discussions about the issue on Tom Kyte's site about it (asktom.oracle.com), if you want to kill the afternoon reading them.
Re: Diff between count(1) and count(*) [message #201253 is a reply to message #201252] Fri, 03 November 2006 05:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But there IS a difference! Are you blind?!? 1 <> * ./fa/1587/0/

OK, here's discussion on Ask Tom about it.
Re: Diff between count(1) and count(*) [message #201255 is a reply to message #201246] Fri, 03 November 2006 05:27 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Where count does return different values is in the following scenario:

select count(column)
from table

Note this will only count the non-NULL values of column. Count(*) always counts all rows (even if every column in the table is null) and count(1) also counts all rows, because 1 is clearly never null.
Previous Topic: managing errors from script
Next Topic: Why the difference?
Goto Forum:
  


Current Time: Fri Dec 09 12:06:13 CST 2016

Total time taken to generate the page: 0.12163 seconds