In SQL, what's the difference between count(column) and count(*)?
count(*)
counts NULLs and count(column)
does not
[edit] added this code so that people can run it
create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)
select count(*),count(id),count(id2)
from #bla
results
7 3 2
In SQL is there a difference between count(*) and count(fieldname)
Count(*) counts all records, including nulls, whereas Count(fieldname) does not include nulls.
count(*) vs count(column-name) - which is more correct?
COUNT(*)
counts all rowsCOUNT(column)
counts non-NULLs onlyCOUNT(1)
is the same asCOUNT(*)
because 1 is a non-null expressions
Your use of COUNT(*)
or COUNT(column)
should be based on the desired output only.
What is the difference between COUNT(*) and COUNT(table.ColumnName)?
The difference between these two is not (primarily) performance. They count different things:
COUNT(*)
counts the rows in your table.
COUNT(column)
counts the entries in a column - ignoring null
values.
Of course there will be performance differences between these two, but that is to be expected if they are doing different things. Especially when the column allows null
-values, the query will take longer than on a column that does not (or COUNT(*)
).
In SQL, what’s the difference between count(*) and count('x')?
To say that SELECT COUNT(*) vs COUNT(1)
results in your DBMS returning "columns" is pure bunk. That may have been the case long, long ago but any self-respecting query optimizer will choose some fast method to count the rows in the table - there is NO performance difference between SELECT COUNT(*), COUNT(1), COUNT('this is a silly conversation')
Moreover, SELECT(1) vs SELECT(*)
will NOT have any difference in INDEX usage -- most DBMS will actually optimize SELECT( n ) into SELECT(*)
anyway. See the ASK TOM: Oracle has been optimizing SELECT(n) into SELECT(*)
for the better part of a decade, if not longer:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789
problem is in count(col) to count()
conversion
**03/23/00 05:46 pm *** one workaround is to set event 10122 to
turn off count(col) ->count()
optimization. Another work around is
to change the count(col) to count(),
it means the same, when the col has a
NOT NULL constraint. The bug number is
1215372.
One thing to note - if you are using COUNT(col) (don't!) and col is marked NULL, then it will actually have to count the number of occurrences in the table (either via index scan, histogram, etc. if they exist, or a full table scan otherwise).
Bottom line: if what you want is the count of rows in a table, use COUNT(*)
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
difference between select count(*) and select count(fieldName)
The difference between the functions COUNT(*)
and COUNT(fieldname)
is that the second does not calculate NULL-values.
SQL Query: Which one should i use? count(columnname) or count(1)
There can be differences between count(*)
and count(column)
. count(*)
is often fastest for reasons discussed here. Basically, with count(column)
the database has to check if column
is null or not in each row. With count(column)
it just returns the total number of rows in the table which is probably has on hand. The exact details may depend on the database and the version of the database.
Short answer: use count(*)
or count(1)
. Hell, forget the count and select userid
.
You should also make sure the where
clause is performing well and that its using an index. Look into EXPLAIN
.
whats faster, count(*) or count(table_field_name) in mysql?
The difference is Count(field)
returns count of NOT NULL values in the field, whether COUNT(*)
returns COUNT of rows.COUNT(*)
in MyIsam should be faster.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count
What does select count(1) from table_name on any database tables mean?
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(*).
Related Topics
Sql: How to Find Duplicates Based on Two Fields
SQL Select Max(Date) and Corresponding Value
Add Row Number to This T-SQL Query
SQL Server: How to Know If Any Row Is Referencing the Row to Delete
Oracle SQL: How to Use More Than 1000 Items Inside an in Clause
Calculate Row Wise Sum - SQL Server
How to Check If Identity_Insert Is Set to on or Off in SQL Server
How to Get the Raw Query String from Laravel's Query Builder Before Executing the Query
Why Isn't Row Level Security Enabled for Postgres Views
Date_Trunc 5 Minute Interval in Postgresql
Oracle Row Count of Table by Count(*) VS Num_Rows from Dba_Tables
Update with Case and in - Oracle
The Version of SQL Server in Use Does Not Support Datatype Datetime2
Anonymizing Customer Data for Development or Testing
Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns