4

Why do we need to use query SELECT COLUMN1, COLUMN2 when we can just use SELECT * and only show the columns we want?

And what's the difference between SELECT ALL and SELECT?

Nicklas Börjesson
  • 433
  • 3
  • 12
Joni Lelet
  • 47
  • 6

3 Answers3

3

The question is, why would one use SELECT col1, col2 when you can easily just show what columns you like in the application and always use SELECT *?

The main reasons are these:

  1. Selecting only the columns you want means that the server has to gather and send less data to the application, making for a faster query and less resource usage, especially if you have many columns or some of them contain BLOBs.
  2. If the structure in the database changes(for example if a column is renamed or dropped, you get more easily understood errors, and they appear in the select, not the application.
  3. If you want to use the fields in the result by their integer index, you must specify the columns, or else you are at the mercy of whatever column order the query optimization comes up with. Your application may just stop working because someone added a column or index.

Edit to answer the second part of the question:

Oh, and the difference between SELECT ALL and SELECT is that there is no difference.
SELECT ALL is the default behaviour of SELECT in all dialects I know of.

However, SELECT DISTINCT is different. It returns the unique values of a set, so if you run :

SELECT DISTINCT col1, col2

on a table that contains

1,2
1,2
2,2
2,3

you will get

1,2
2,2
2,3
Nicklas Börjesson
  • 433
  • 3
  • 12
  • 1
    "If you want to use the fields in the result by their integer index" --- if one does that at first place - I find it's a fair punishment for following terrible practices. – zerkms Jan 11 '15 at 21:52
  • Sorry for the over-editing, but just wanted to really drive that one home. Have seen so many things blow up in my days. :-) – Nicklas Börjesson Jan 11 '15 at 21:53
  • Actually, it might not be. There are several use cases for that. – Nicklas Börjesson Jan 11 '15 at 21:53
  • @zerkms: Realized that I'd better explain that. Examples: 1. if you dynamically create views in your application that you match with generated SQL:s and so forth. 2. For speed; some data sets have really stupid field lookups and does a look up every row, although I hope that is uncommon these days. 3. there are pivot:s that you build. 4. And then of course, there are silly legacy applications that simply force you to do things like that. – Nicklas Börjesson Jan 11 '15 at 22:01
  • @NicklasBörjesson I completely agree with you actually (and the only upvote you currently have is the one I did :-), I just tried to joke that using positional columns is so bad so that it's fair that someone is being bitten by that – zerkms Jan 11 '15 at 22:27
  • All is fair in love and SQL. :-) – Nicklas Börjesson Jan 11 '15 at 22:41
2

There are a lot of questions posted before comparing differences of SELECT Col1, Col2 vs SELECT *

You can find some of them here.

Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

Is there a difference between Select * and Select

select * vs select column

About SELECT ALL and SELECT: There is no difference between these two statements and you can use SELECT Col1, Col2 or SELECT ALL Col1, Col2

If you have a look at SELECT statement:

SELECT [ALL | DISTINCT] column1[,column2] FROM table1

ALL is default.

Community
  • 1
  • 1
sqluser
  • 5,502
  • 7
  • 36
  • 50
0

If you have a big table with a lot of columns and you need only one of them "SELECT COLUMN" will be executed faster then "SELECT ALL".

Nikola
  • 131
  • 1
  • 6
  • What is a "big table"? – zerkms Jan 11 '15 at 21:43
  • Table with a lot of data – Nikola Jan 11 '15 at 21:48
  • 1
    If you have a table with 1 billion rows but only select 1 row from it: why number of rows matters (in the context of the question)? – zerkms Jan 11 '15 at 21:51
  • In the context of the question, I wasn't able to find or understand how many rows will be selected. – Nikola Jan 11 '15 at 22:02
  • My point was: it's a size of a result set that matters, not the size of the table in total. You may have a tiny table with 1000 rows, joined 2 times with itself which results a cartesian product of 10^9 rows. So, small table, big result set. – zerkms Jan 11 '15 at 22:25