When we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
views:
6158answers:
9Does the 1 refer to column one?
dacracot
2008-10-08 03:56:24
no, 1 refers to the constant number 1. When you say, ORDER BY 1, it refers to column one.
Thilo
2008-10-08 03:57:29
that's what I wondered a while back, but you can specify any number which is greater than the column count, still the result is same. So its definitely not column number.
Nrj
2008-10-08 03:58:01
@dacracot: it may but i don't believe so. From what i understand oracle just re-writes count(1) to be count(*) in the background
Jarod Elliott
2008-10-08 03:58:22
+1
A:
This is similar to the difference between SELECT * FROM table_name and SELECT 1 FROM table_name. If you do SELECT 1 FROM table_name it will give you the number 1 for each row in the table. So yes count(*) and count(1) will provide the same results as will count(8) or count(column_name)
ChrisHDog
2008-10-08 03:57:09
count(column_name) is not quite the same - it doesn't count rows with null in that column. See here for details: http://stackoverflow.com/questions/169784
Blorgbeard
2008-10-08 04:19:28
Hmm, actually that's for SQL Server. Oracle may work differently I suppose.
Blorgbeard
2008-10-08 04:21:39
Count should never use NULL values as per standard SQL, ORacle and SQL Server should have the same behaviour in this regard.
IronGoofy
2008-10-08 04:58:16
"similar to the difference between SELECT * FROM table_name and SELECT 1 FROM table_name." -- not really. SELECT * and SELECT 1 are different. SELECT (*) and SELECT (1) give the same result.
David Aldridge
2008-10-08 13:38:17
+3
A:
There is no difference.
COUNT(1) is basically just counting a constant value 1 column for each row. As other users here have said, it's the same as COUNT(0) or COUNT(42). Any non-NULL value will suffice.
http://asktom.oracle.com/pls/asktom/f?p=100:11:2603224624843292::::P11_QUESTION_ID:1156151916789
The Oracle optimizer did apparently use to have bugs in it, which caused the count to be affected by which column you picked and whether it was in an index, so the COUNT(1) convention came into being.
Cade Roux
2008-10-08 03:57:23
+22
A:
The parameter to the COUNT function is an expression that is to be evaluated for each row. The COUNT function returns the number of rows for which the expression evaluates to a non-null value. ( * is a special expression that is not evaluated, it simply returns the number of rows.)
There are two additional modifiers for the expression: ALL and DISTINCT. These determine whether duplicates are discarded. Since ALL is the default, your example is the same as count(ALL 1), which means that duplicates are retained.
Since the expression "1" evaluates to non-null for every row, and since you are not removing duplicates, COUNT(1) should always return the same number as COUNT(*).
Jeffrey L Whitledge
2008-10-08 04:00:28
A:
SELECT COUNT(1) should do the exact same thing as SELECT COUNT(*).
There may have been or still be some reasons why it would perform better than SELECT COUNT(*) on some database, but I would consider that a bug in the DB.
SELECT COUNT(col_name) however has a different meaning, as it counts only the rows with a non-null value for the given column.
Thilo
2008-10-08 04:01:15
A:
Depending on who you ask, some people report that executing select count(1) from random_table; runs faster than select count(*) from random_table. Others claim they are exactly the same.
This link claims that the speed difference between the 2 is due to a FULL TABLE SCAN vs FAST FULL SCAN.
Johann Zacharee
2008-10-08 04:04:11
You're misinterpreting the link. LS is making the point that COUNT(COLUMN) can be the same as and faster than COUNT(*), but only when no NOT NULL columns are indexed but COLUMN is indexed and actually has no null values. More of a trick question.
David Aldridge
2008-10-08 13:41:54
I was trying to use the link to document the claim that, in some circumstances, COUNT(1) could be faster than COUNT(*).Some earlier answers had made the statement that there were no differences between the 2, and I was providing a possible counter example.
Johann Zacharee
2008-10-08 15:15:09