Question: SELECT * gives error when executed alone, but SELECT COUNT (*) does not. Why?
Please leave your answer as a comment over here. If you prefer, you can blog post about this on your blog and put a link here. I will publish valid answer with due credit in future blog posts. Remember one should not use SELECT * in the production environment as there are many different disadvantages of the SELECT *. The primary disadvantage is that it retrieves the data most of the time which users are not using normally in the production display. For example, not all the time we need to retrieve last updated time or even GUID if they exists on the table. The purpose of this blog post was for demonstration purpose and just for fun.
Reference: Pinal Dave (https://blog.sqlauthority.com)
76 Comments. Leave new
Dera All,
Select * is returning the error….
Select Count(*) it is showing result it as 1 if we are not mentioned the table or if the table does not having the records..
Select Count(1) is also same as above…
select Count(‘Some text’) this is also returning the same as above two…
My quiestion is What is the use of ‘*’ in this….
‘*’ means it will return all the row in the table as of my knowledge… but in this select count(‘some text’) the text is acting like a *
Anybody Plz clarify this……
I think, SGA (System Global Area) has no definition for * when we use this alone.
COUNT is function thats why its returning value on SELECT
I guess, SELECT COUNT(*) is working like other scalar functions which we can use in select statement. Like SELECT LEFT(‘SQL Authority’, 3) etc. If we write SELECT COUNT(‘SQL’) it will return 1 too. I guess when FROM clause is missing, the COUNT function is counting the number of parameter or I can say what is has inside the () and every time it will return 1.
SELECT * always expect a table/set. That’s why it is getting error.
Hi ,
I agree with Rodrigo Ribeiro .. count(*) is just counting the rows . It just counts how may rows returned . For aggregation functions there is no need to specify the table name , only argument is sufficient .
But select “*” requires table name to return the data ..
But i am surprised how this
select ‘blah’ where exists (select *) is working , could you please also explain this ..
Hey, pinal, save us time and give the answer :)
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.
Thanks,
Ramesh Vagh,
Software Engineer
here’s a simple view…
the select * command returns all columns from all rows in the table.
if any data value in any row and/or column is corrupt or has a problem an error can be returned.
there is also a higher probability of a hardware problem from returning all the data from the table as select * does.
the select count(*) command only returns the count of rows in the table.
as the select count(*) command does not return any actual data from the table, data corruption will likely not impact the select (*) command.
have a nice weekend :-)
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.
Answer is very simple.
as when you are trying to Execute “Select *”, you must specify table name in from Clause. because * awaits for Table to give outputs of Columns and Data. when you are trying to execute Select *, this * means what ?
You have to specify query or Table to take output. so you will get an error when you will try to execute only “Select * ”
now, the 2nd question is for : Select Count(*), it suppose to give you Count of Rows, it means if you try to Execute Select Count(1) or Select Count(*) or Select Count(Any Variable). then the output will be 1. same for all queries.
When you say “SELECT *” you are writing a partial command: “SELECT these fields” without saving where they should come from. “*” is a wildcard which expands to “all fields” so you need to specify the table to read from.
When you say “SELECT COUNT(*)” you are a different command: a SELECT that returns a single item, the COUNT, of the number of items. Because there is no list, the result comes out at 1 – the count itself.
In My view,
According to C++ ‘*'(asterick) is De reference operator which means “point to” the variable whose reference they store. it requires a reference. so here i mean in SQL we refer a
And Count() is behaves like a function it requires 1 argument.
For ex: select count(0) in place of count(*) it returns a value.
In My view,
According to C++ ‘*’(asterick) is De reference operator which means “point to” the variable whose reference they store. it requires a reference. so here i mean in SQL we refer a
And Count() is behaves like a function it requires 1 argument.
For ex: select count(0) in place of count(*) it returns a value.
In My view,
According to C++ ‘*’(asterick) is De reference operator which means “point to” the variable whose reference they store. it requires a reference. so here i mean in SQL we refer a tablename
And Count() is behaves like a function it requires 1 argument.
For ex: select count(0) in place of count(*) it returns a value.
COUNT always return a integer value because it return number of rows
hi,
plz anybody reply me,
i got an error of 14262 in log shipping configuration
actually what i done is iam just started using sql server 2005, i configured the logshipping by using two instances of sql 2005 , instead of disabling the logshipping i deleted the jobs then again when i went to configure logshipping it is not possible i got an error of 14262.(email removed)
Select Count(*) is an aggregate function used to detrmine the number of times the argument occurs, it will return 1 as default.
Select * will throw an error because * is a wild character and not a function and function returns a value.
Hi Pinal,
i am new to sql server and was browsing for the solution.
found a link in msdn
so do you agree with those comments??
Hi All,
I have one question :)
Please execute the following Queries…!
SELECT COUNT(NULL) — this will generate error
GO
DECLARE @A INT
SET @A=NULL
SELECT COUNT(@A) ––this will not generate error why??
I think count function expect table or variable.
Select count(null)– Here count is not able to identify table or variable
Second example do not give error.
also count do not count null values.It shows 0 for it.
in your example,if values of @A is 0 or above then its count is 1.
See one more example,
declare @t table(a varchar(10))
insert into @t values (null)
insert into @t values (”)
insert into @t values (‘abc’)
Select COUNT(a) from @t –Output is 2 because count() do not count null values
If we write,
Select count(*) from @t–Output is 3,because here we are telling count() to provide number of rows.