Earlier this weekend I have presented at Bangalore User Group on the subject of SQL Server Tips and Tricks. During the presentation I have asked a question to attendees. It was very interesting to see that I have received various different answer to my question. Here is the same puzzle for you and I would like to see what your answer to this question.
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
I agree with amikolaj.
* is replaced with the COLUMN-list of the TABLE. If there is no TABLE, there is nothing to replace.
COUNT(*) means COUNT everything in the output. The * is not replaced. Hence, there is no error.
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 *
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 mandatory to provide a table.
Check it out this:
DECLARE @cnt INT
SET @cnt = COUNT(*)
SELECT @cnt
SET @cnt = COUNT(‘x’)
SELECT @cnt
1. (Select *)
Select Retrieves rows from the database & enables to
select one or many rows or columns from one or many
tables in SQL Server.
here Select * lacks the basic structure of from and table name so give error .
2. Select Count(*)
Count is an aggregate function that returns the number of items in a group.count always return an integer data value
Expression is any type except text, image, or ntext. Aggregate functions and subqueries are not permitted
so when we queried Select Count(*) it returns the integer value.
Select *- Here After SELECT clause * is neither an expression nor a column name associated to table/view etc. so SELECT clause fails to identify *, hence error.
Where as
Select Count(*) – If we see the general syntax of COUNT(*) is :COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) , COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column (from MSDN) so it is valid expression to SELECT cluse and hence query analyzer will understand it and hence give the result as 1 without error.
Both select * and select count(*) requires a table name. However select count(*) does not throw an error. This is because count is an aggregate function and it will always return a value!!
I am agree with Sharath
Actually select * and select count(*) requires a table name.
select count(*) does not give an error because aggregate function always return a value.
This is my opinion.
Aggregate functions always return a value but why, in this case, 1 instead of 0 ???
Regardless that this sintax doesn’t result in a error the behavior is not expected and, in my opinion, doesn’t make any sence.
if you run
SELECT COUNT(*) FROM TableName WHERE 1 1
the result is 0
if you run
SELECT COUNT(*)
the result is 0!
we can discuss why SELECT COUNT(*) doesn’t give an error but the result of the script isn’t logical
Select * requires a table name, when parsing the statement it results in an error.
SELECT COUNT(*) is like using SELECT GETDATE(), it is executing a function, hence there is no error
is not the same, you can do
print getdate()
but not
print count(*)
Thank you, what i meant was it is similar.
Count (param) is a aggregate function in T-Sql similar to
sum( param), Max(param) etc….
None of the aggregate functions requires table (or From clause)
* (star) is an operand in sql which means :
1. Multiplication operand
2. dataset’s All columns (when used individually in select query)
3. join operand (=* or *= etc…)
so if * has to be used in a query it should server any one purpose above.
In “select * ” query none of the mentioned purposes are server hence error.
In “select count(*)” , * acts like param to aggregate function. This aggregate function does not require any tables in the list hence it retuns some results.
Hi Pinal,
thanks for the observation.
my opinion is here:
Select count(*) ,
select count(0) ,
select count(1) , and
select count(”) all returns value [1]. because count() will always take not NULL expression.
and if we try to pass any NULL exp. then it will return [0].
Run the following
see:
select cast(null as int) output [NULL]
test:
select count(cast(null as int)) output [0]
so the my assumption is that because of “asterisk *” is special keyword or wildcard character in SQL and treating it as not NULL expression just like other values with the combination of count() function.
just look at this:
select count($) output[1]
output is 1 again and $ sign is also treated as not NULL expression with combination.
and if we run
Select *
output :Msg 263, Level 16, State 1, Line 1
Must specify table to select from.
where select * is expecting a combination of [from table name].
“Select * ” gives error message b’cas for the “select ” command its compulsory to have column_name(for all columns; we use *) and table_name with From.
B’cas it’s syntax is prepared that.
But in case of “Select Count(*)”, Count is an aggregate function which requires a paramater which is not null then it gives 1 and not gives error message.
Select Count(*) is an aggregate function used to detrmine the number of times the argument occurs, hence 1.
Try Select Count(20) or any other argument and the result would be 1.
When used in conjunction with a table or view it checks for the ocuurances within the object. My two bits
Hi Pinal,
Now lot of responses have come out. Eagerly waiting for your answer.
Binu
Can you post the answer to this question.
select 1 — output– 1
select 0 — output– 0
select ‘a’ — output– a
where as
select * –here * is not included in ‘ ‘ for char type so it will through a message
and
select count(1)–1
select count(2)–1
select count(*)–1 here *, 1 , 2 are items so COUNT(*) returns the number of items in a group
Without FROM clause SELECT works to evaluate the expression. As per the SQL Server standard ‘*’ is used to list all the columns of the table/view/function(tablular). If table is absent in the SELECT clause then definitely it will raise an error. While executing COUNT(*) without FROM Clause COUNT function evaluates the input parameter and work accordingly. In other word I would say aggregate function COUNT takes care its input parameters based on its limit.
select count(*) with no table uses a “constant scan”. The constant scan produces a single row for the aggregate function to populate since no other rows exist. Since there is no table, the only row is the row produced by the constant scan so you get a count of 1. You can see this if you look at the execution plan.
following up on my previous post, I believe a constant scan will be used to generate a record anytime sql server needs a record to exist before it can evaluate the entirety of a tsql statement. This is why the statement
select ‘blah’ where exists (select *)
also produces a record when select * by itself does not.
Hi,
Both Select * and Select count(*) will create temporary datasets to display the results to the user. Select * will populate the dataset with the error message, since it expects the table name and columns, the error message is displayed to the user where as Select count(*) will count the number of rows in the dataset, which is always 1 for this case. The one row, one column which has the error message in it.
Hence Select * returns the error and Select Count(*) returns 1.
Thanks,
Rajshekhar
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.