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.
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
What is the difference between count (*) and count(attribute_name)?
Imagine this table:
select Count(TelephoneNumber) from Calls -- returns 3
select Count(*) from Calls -- returns 4
count(column_name) also counts duplicate values. Consider:
select Count(TelephoneNumber) from Calls -- returns 4
In SQL is there a difference between count(*) and count( fieldname )
Count(*) counts all records, including nulls, whereas Count(fieldname) does not include nulls.
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(*)
).
count(*) and count(column_name), what's the diff?
COUNT(*)
counts all rows in the result set (or group if using GROUP BY).COUNT(column_name)
only counts those rows wherecolumn_name
is NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable).COUNT(1)
is the same asCOUNT(*)
since 1 can never be NULL.
To see the difference in the results you can try this little experiment:
CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
COUNT(*) AS a,
COUNT(x) AS b,
COUNT(1) AS c
FROM table1;
Result:
a b c
3 2 3
difference between count(*) and count(columnName)
There's at least one difference.
- They may return different results if
email
can containNULL
.
For more information, see this article.
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(*)
Related Topics
How to Log SQL Statements in Grails
Delete Duplicate Records from a SQL Table Without a Primary Key
The Maximum Recursion 100 Has Been Exhausted Before Statement Completion
Using Excel Vba to Export Data to Ms Access Table
What's the Best Way to Select the Minimum Value from Several Columns
How Can Multiple Rows Be Concatenated into One in Oracle Without Creating a Stored Procedure
Calculating Number of Full Months Between Two Dates in SQL
T-SQL Split String Based on Delimiter
How to Assign an Exec Result to a SQL Variable
SQL Rownum How to Return Rows Between a Specific Range
SQL Server:Transpose Rows to Columns
Using 'Case Expression Column' in Where Clause