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.
RGarvao
https://connect.microsoft.com/SQLServer/feedback/details/671475/select-test-where-exists-select
tiberiu utan
http://connect.microsoft.com/SQLServer/feedback/details/338532/count-returns-a-value-1
Rob Farley
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…
Koushik
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.
amikolaj
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.
Netra Acharya
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
Naveen
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.
Ramesh
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.
Sachin Nandanwar
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 (http://blog.SQLAuthority.com)
Filed under: CodeProject, Pinal Dave, PostADay, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology
