what is difference between select * from and select field name

Posted by Ankit_Parmar555 under Sql Server on 10/23/2013 | Points: 10 | Views : 1962 | Status : [Member] | Replies : 9
what is difference between select * from and select field name
kindly expalin




Responses

Posted by: Muhsinathk on: 10/23/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi,
'Select * from tablename;' this returns all the table values.But
'Select column1,column2,....from tablename;' returns specific column on that columns.



Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT * FROM tableName;
--> returns all columns data available in the table named as "tableName"
--> You can not alias the column names
--> You can not do calculation with *

SELECT FieldName FROM TableName;
--> returns specific column (named as "FieldName" ) data
--> You can alias the column names
SELECT FieldName AS AliasFieldName FROM tableName;
--> You can do calculation
For example, you can find out annual salary from the salary column
SELECT 12*Salary AS AnnualSal FROM TableName;


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 10/23/2013 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
To add @Muhsinathk and @Bandi,

Select * should be avoided from performance point of view, as even if you just have to use one column; you are unnecessary returning all columns from the database that consumes processing power and resources of the system.

So better to always use Column names with the Select statement, unless you know that you are going to use all columns of the database table.

Thanks


Regards,
Sheo Narayan
http://www.dotnetfunda.com

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 10/24/2013 [Member] Bronze | Points: 25

Up
0
Down
Please mark as answer if it helpful to you. That helps others who search the same..

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 10/24/2013 [Member] Starter | Points: 25

Up
0
Down
only performance

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Samirbhogayta on: 10/28/2013 [Member] Starter | Points: 25

Up
0
Down
hi..

with select * you can retrieve all the fields of a table, when with select field name you can retrieve specific fields which you want to retrieve.

Thanks

SAMIR
Sr. Software Engineer

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ankit_Parmar555 on: 10/29/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks all for this reply but actual concern of my question is like this :

i want all field in result.
for that i use two query

1. select * from table name
2. select all column name from table name.

Then what is difference between this two queries ?

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 10/29/2013 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
There is no difference in that case.

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/29/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
There is no specific difference between those two queries...
For best practices better to specify the all column names in the SELECT query ...
In the following case you might need to modify the code ( if you use select * ):
Whenever new columns added to the table you must get application errors

Click on "Mark as Answer " if you got cleared the doubt

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ankit_Parmar555, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response