Earlier I have published Puzzle Why SELECT * throws an error but SELECT COUNT(*) does not.
This question have received many interesting comments. Let us go over few of the answers, which are valid. Before I start the same, let me acknowledge Rob Farley who has not only answered correctly very first but also started interesting conversation in the same thread.
The usual question will be what is the right answer. I would like to point to official Microsoft Connect Items which discusses the same.
https://connect.microsoft.com/SQLServer/feedback/details/671475/select-test-where-exists-select
http://connect.microsoft.com/SQLServer/feedback/details/338532/count-returns-a-value-1
count(*) is about counting rows, not a particular column. It doesn’t even look to see what columns are available, it’ll just count the rows, which in the case of a missing FROM clause, is 1.
“select *” is designed to return columns, and therefore barfs if there are none available.
Even more odd is this one:
select ‘blah’ where exists (select *)
You might be surprised at the results…
The engine performs a “Constant scan” for Count(*) where as in the case of “SELECT *” the engine is trying to perform either Index/Cluster/Table scans.
When you query ‘select * from sometable’, SQL replaces * with the current schema of that table. With out a source for the schema, SQL throws an error.
so when you query ‘select count(*)’, you are counting the one row. * is just a constant to SQL here. Check out the execution plan. Like the description states – ‘Scan an internal table of constants.’
You could do ‘select COUNT(‘my name is adam and this is my answer’)’ and get the same answer.
SELECT *
Here, * represents all columns from a table. So it always looks for a table (As we know, there should be FROM clause before specifying table name). So, it throws an error whenever this condition is not satisfied.
SELECT COUNT(*)
Here, COUNT is a Function. So it is not mandetory to provide a table.
Check it out this:
DECLARE @cnt INT
SET @cnt = COUNT(*)
SELECT @cnt
SET @cnt = COUNT(‘x’)
SELECT @cnt
Select 1 / Select ‘*’ will return 1/* as expected.
Select Count(1)/Count(*) will return the count of result set of select statement.
Count(1)/Count(*) will have one 1/* for each row in the result set of select statement.
Select 1 or Select ‘*’ result set will contain only 1 result. so count is 1.
Where as “Select *” is a sysntax which expects the table or equauivalent to table (table functions, etc..). It is like compilation error for that query.
Hi Friends,
Count is an aggregate function and it expects the rows (list of records) for a specified single column or whole rows for *.
So, when we use ‘select *’ it definitely give and error because ‘*’ is meant to have all the fields but there is not any table and without table it can only raise an error.
So, in the case of ‘Select Count(*)’, there will be an error as a record in the count function so you will get the result as ’1′.
Try using : Select COUNT(‘RAMESH’) and think there is an error ‘Must specify table to select from.’ in place of ‘RAMESH’
Pinal : If i am wrong then please clarify this.
Any aggregate function expects a constant or a column name as an expression.
DO NOT be confused with * in an aggregate function.The aggregate function does not treat it as a column name or a set of column names but a constant value, as * is a key word in SQL.
You can replace any value instead of * for the COUNT function.Ex
Select COUNT(5) will result as 1.
The error resulting from select * is obvious it expects an object where it can extract the result set.
I sincerely thank you all for wonderful conversation, I personally enjoyed it and I am sure all of you have the same feeling.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi Pinal,
Mine answer was also correct with diffeent scenarios. I wrote –
Both SELECT * and SELECT COUNT(*) requires table name to be read.
SELECT * gives list of records with all columns from the table.
SELECT COUNT(*) gives the count of resulting rows. Here, SELECT COUNT(*) is also giving an error for table name but returns a single row contains error message. Hence, SELECT COUNT(*) gives 1 as an output.
— Shekhar
Hi Pinal,
Is there any possibility to convert table row values into Columns by using SQL Query, If yes, then please could you explain more on that.
Thanks in advance.
RaviPola.
Use PIVOT
hi Pinal,
if we fire “select count(*) OR select count(‘asd’) OR select count(123)”
still it reply with 1.
as well all know * stands for ALL.
when we fire select * we must specify from where.
COUNT is an inbuilt aggregate function. and it require 1 argument to execute.
i think it returns 1(true) bcoz it execute in sql engine.
it need 1 argument. and it execute successfully.
it return the query value if we specify from
else it return function executed successfully (true i.e 1).
I have connect my application SQL database.Based on data application is hanging is it future any other way is there to connect or increase capacity in SQL 2005
something strange i have noticed regarding the count(*) while used with the group by
it always return 1 if the table is empty
i have this code
update tbl1
set tbl1.eleCount = tbl2.VoteCount
FROM tblNames tbl1
INNER JOIN
(select eleNum, count(*) AS VoteCount
from tblEntries
GROUP BY eleNum)tbl2
ON tbl1.eleNum = tbl2.eleNum
i use this in after Delete trigger in tblEntires table
if there is no records at all in the tblEntires all the eleNum records in the tblNames are set to 1vote …. it never update to zero
how can this be solved?