Count(*) vs Count(1) - SQL Server
There is no difference.
Reason:
Books on-line says "
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
"
"1" is a non-null expression: so it's the same as COUNT(*)
.
The optimizer recognizes it for what it is: trivial.
The same as EXISTS (SELECT * ...
or EXISTS (SELECT 1 ...
Example:
SELECT COUNT(1) FROM dbo.tab800krows
SELECT COUNT(1),FKID FROM dbo.tab800krows GROUP BY FKID
SELECT COUNT(*) FROM dbo.tab800krows
SELECT COUNT(*),FKID FROM dbo.tab800krows GROUP BY FKID
Same IO, same plan, the works
Edit, Aug 2011
Similar question on DBA.SE.
Edit, Dec 2011
COUNT(*)
is mentioned specifically in ANSI-92 (look for "Scalar expressions 125
")
Case:
a) If COUNT(*) is specified, then the result is the cardinality of T.
That is, the ANSI standard recognizes it as bleeding obvious what you mean. COUNT(1)
has been optimized out by RDBMS vendors because of this superstition. Otherwise it would be evaluated as per ANSI
b) Otherwise, let TX be the single-column table that is the
result of applying the <value expression> to each row of T
and eliminating null values. If one or more null values are
eliminated, then a completion condition is raised: warning-
COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
Bottom Line
Use either COUNT(field)
or COUNT(*)
, and stick with it consistently, and if your database allows COUNT(tableHere)
or COUNT(tableHere.*)
, use that.
In short, don't use COUNT(1)
for anything. It's a one-trick pony, which rarely does what you want, and in those rare cases is equivalent to count(*)
Use count(*)
for counting
Use *
for all your queries that need to count everything, even for joins, use *
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
But don't use COUNT(*)
for LEFT joins, as that will return 1 even if the subordinate table doesn't match anything from parent table
SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Don't be fooled by those advising that when using *
in COUNT, it fetches entire row from your table, saying that *
is slow. The *
on SELECT COUNT(*)
and SELECT *
has no bearing to each other, they are entirely different thing, they just share a common token, i.e. *
.
An alternate syntax
In fact, if it is not permitted to name a field as same as its table name, RDBMS language designer could give COUNT(tableNameHere)
the same semantics as COUNT(*)
. Example:
For counting rows we could have this:
SELECT COUNT(emp) FROM emp
And they could make it simpler:
SELECT COUNT() FROM emp
And for LEFT JOINs, we could have this:
SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
But they cannot do that (COUNT(tableNameHere)
) since SQL standard permits naming a field with the same name as its table name:
CREATE TABLE fruit -- ORM-friendly name
(
fruit_id int NOT NULL,
fruit varchar(50), /* same name as table name,
and let's say, someone forgot to put NOT NULL */
shape varchar(50) NOT NULL,
color varchar(50) NOT NULL
)
Counting with null
And also, it is not a good practice to make a field nullable if its name matches the table name. Say you have values 'Banana', 'Apple', NULL, 'Pears' on fruit
field. This will not count all rows, it will only yield 3, not 4
SELECT count(fruit) FROM fruit
Though some RDBMS do that sort of principle (for counting the table's rows, it accepts table name as COUNT's parameter), this will work in Postgresql (if there is no subordinate
field in any of the two tables below, i.e. as long as there is no name conflict between field name and table name):
SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
But that could cause confusion later if we will add a subordinate
field in the table, as it will count the field(which could be nullable), not the table rows.
So to be on the safe side, use:
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
count(1)
: The one-trick pony
In particular to COUNT(1)
, it is a one-trick pony, it works well only on one table query:
SELECT COUNT(1) FROM tbl
But when you use joins, that trick won't work on multi-table queries without its semantics being confused, and in particular you cannot write:
-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
So what's the meaning of COUNT(1) here?
SELECT boss.boss_id, COUNT(1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Is it this...?
-- counting all the subordinates only
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Or this...?
-- or is that COUNT(1) will also count 1 for boss regardless if boss has a subordinate
SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
By careful thought, you can infer that COUNT(1)
is the same as COUNT(*)
, regardless of type of join. But for LEFT JOINs result, we cannot mold COUNT(1)
to work as: COUNT(subordinate.boss_id)
, COUNT(subordinate.*)
So just use either of the following:
-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Works on Postgresql, it's clear that you want to count the cardinality of the set
-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Another way to count the cardinality of the set, very English-like (just don't make a column with a name same as its table name) : http://www.sqlfiddle.com/#!1/98515/7
select boss.boss_name, count(subordinate)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
You cannot do this: http://www.sqlfiddle.com/#!1/98515/8
select boss.boss_name, count(subordinate.1)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
You can do this, but this produces wrong result: http://www.sqlfiddle.com/#!1/98515/9
select boss.boss_name, count(1)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
SQL Best Practice: count(1) or count(*)
According to another similar question (Count(*) vs Count(1)), they are the same.
In Oracle, according to Ask Tom, count(*) is the correct way to count the number of rows because the optimizer changes count(1) to count(*). count(1) actually means to count rows with non-null 1's (all of them are non-null so optimizer will change it for you).
What does count(1) in SQL mean?
Basically, count(1)
produces just the same result as count(*)
: that is, it counts the number of records in the group defined by the group by
clause.
Why? count(<expr>)
counts every non-null
value of <expr>
. Here it is given a constant value, 1
, that is never null
- so it counts all rows.
I wouldn't recommend count(1)
; checking for nullity of each and every row requires more work for the database that just counting all rows. Some databases might optimize count(1)
as count(*)
under the hood (as it is obvious that this fixed expression will never be null
), but why bother, when you can optimize yourself already? Just use count(*)
consistently.
Difference between count(1) and count(*) in oracle
I believe count(1)
used to be faster in older versions of Oracle. But by now, I'm pretty sure the optimizer is smart enough to know that count(*)
and count(1)
mean you want the number of rows and creates an appropriate execution plan.
Here you go:
create table t as select * from all_objects;
Table T created.
create index tindx on t( object_name );
Index TINDX created.
select count(*) from t;
COUNT(*)
----------
21534
select * from table(dbms_xplan.display_cursor( NULL, NULL, 'allstats last' ));
Plan hash value: 2940353011
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 100 | 93 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 100 | 93 |
| 2 | INDEX FAST FULL SCAN| TINDX | 1 | 18459 | 21534 |00:00:00.01 | 100 | 93 |
--------------------------------------------------------------------------------------------------
select count(1) from t;
COUNT(1)
----------
21534
Plan hash value: 2940353011
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 100 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 100 |
| 2 | INDEX FAST FULL SCAN| TINDX | 1 | 18459 | 21534 |00:00:00.01 | 100 |
-----------------------------------------------------------------------------------------
So not only is it smart enough to know it can use the index to optimize this query, but it uses the exact same execution plan for the different versions (the plan has value is the same).
What is the difference between count(0), count(1).. and count(*) in mySQL/SQL?
Nothing really, unless you specify a field in a table or an expression within parantheses instead of constant values or *
Let me give you a detailed answer. Count will give you non-null record number of given field. Say you have a table named A
select 1 from A
select 0 from A
select * from A
will all return same number of records, that is the number of rows in table A. Still the output is different. If there are 3 records in table. With X and Y as field names
select 1 from A will give you
1
1
1
select 0 from A will give you
0
0
0
select * from A will give you ( assume two columns X and Y is in the table )
X Y
-- --
value1 value1
value2 (null)
value3 (null)
So, all three queries return the same number. Unless you use
select count(Y) from A
since there is only one non-null value you will get 1 as output
Select Count(*) vs Select Count(id) vs select count(1). Are these indeed equivalent?
count(*) --counts all values including nulls
count(id)-- counts this column value by excluding nulls
count(1) is same as count(*)
If we add a where clause: where msg_type = X; if msg_type has a non_clustered index, would select count(msg_type) from table_name where msg_type = X be the preferred option for counting?
As i mentioned in my previous answer ,SQL server is a cost based optimizer and the plan choosen depends on many factors .sql tries to retrieve cheapest plan in minimum time possible..
now when you issue,count(msg_type)
,SQL may choose this index if this is cheaper or scan another one as long as it gives right
results(no nulls in output)..
I always tend to use count(*)
,unless i want to exclude nulls,for the reasons quoted here
When to use Count(*) and Count(1) in Sql Server 2008
COUNT (*)
is the same as COUNT(1)
. The reality is that the “*”
is actually the default option and it needs not to be specified.The Count(*)
includes all the non-NULL values.If you use the column name or any constant in the COUNT function. it will give you NON-NULL values
Related Topics
Error: Tcp Provider: Error Code 0X2746. During the SQL Setup in Linux Through Terminal
Remove Duplicate Rows in MySQL
How to Use Parameters in Vba in the Different Contexts in Microsoft Access
Count Work Days Between Two Dates
Postgresql "Column Does Not Exist" But It Actually Does
Datetime2 VS Datetime in SQL Server
Sql/MySQL - Select Distinct/Unique But Return All Columns
Sqlite Insert - on Duplicate Key Update (Upsert)
Bash Script to Insert Values in MySQL
SQL Query Return Data from Multiple Tables
You Can't Specify Target Table For Update in from Clause
Only Inserting a Row If It's Not Already There
How to Select the Nth Row in a SQL Database Table
Difference Between Scope_Identity(), Identity(), @@Identity, and Ident_Current()