Click here to Skip to main content
15,073,599 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)

I would to know the difference between select * and select count(*)
If I use select * it gives an error

 "Msg 263, Level 16, State 1, Line 1
Must specify table to select from" 

but select count(*) does not give any error
Instead It gives 1 as count.

May I know the reason.

Thanks in advance.
Updated 29-May-11 23:04pm
ZeeroC00l 30-May-11 5:04am
-- added pre and code tags for readability

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:
"SELECT * FROM myTable"

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.
That's Aragon 30-May-11 5:17am
Good explanation in very simple words.Have my 5 :)
It mean in that table you have only one field and it has only one row.!

so, When you use 'select count(*)' it count no of rows from the table which you used recently.

for 'select * ' - you should mention the table name..!
sacraj 30-May-11 5:26am
I used select * from sample This table has got 5 columns
after this I executed the query "select * from count(*)" It gives an error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
What is problem in my query Please correct.
sivakumar321 30-May-11 5:27am
you can't use 'select * from count(*)' - invalid query..
Count(*) counts all the rows in the result set. Select * returns all columns for the rows in the resultset.

If the count is changing it's probably there are inserts and deletes happening against the destination table.

You can take a look at SQL Tutorial[^], it is a nice and simple way to learn more about SQL statements.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900