Why should SELECT * be avoided in SQL?

Question

I am developing an application and I was reading about how queries work. I read somewhere that you should avoid SELECT * FROM... where blah = blah

Why is that? And what's the workaround if you're trying to select pretty much everything?

Answer 1

Initially need to know what data you will need. Although, you can select all at once, if such requests will not be much. The difference in performance, you'll see only in heavy projects.

Answer 2

This is not really a direct answer to your question "Why is that?" (so downvote the answer if you need to.) It's an answer to the "what's a workaround if you need to" question.

The only workaround to avoid SELECT *, when I need all of the columns in the table, is to get a list of all the columns. And that's just extra busy I work I don't need when I'm already busy.

To put a backwards twist on a line from Office Space charaacter Peter Gibbons: "The thing is, Bob, it's not that I don't care, it's just that I'm lazy."

With MySQL I make for less busy work by using the SQLyog right click menu option to generate a skeleton SELECT statement that contains all the columns.

For a SQL statement that references multiple tables, I want every column reference to be qualified with a table alias, so I'll just use a SQL statement to retrieve a ready-to-use list of columns for me:

SELECT GROUP_CONCAT(CONCAT('t.',c.column_name) 
         ORDER BY c.ordinal_position
       ) AS col_list
  FROM information_schema.columns c
 WHERE c.table_schema = 'mydatabase'
   AND c.table_name = 'mytable'

if I only need a few out a long list, it's easier for me to get them out of a vertical list

SELECT CONCAT(',s.`',c.column_name,'`') AS col_names
  FROM information_schema.columns c
 WHERE c.table_schema = 'mydatabase'
   AND c.table_name = 'mytable'
 ORDER BY c.ordinal_position

When the column references are qualified, the backticks are only needed for "special" characters in column names (or maybe some weird case sensitive setting.)

I can start with that list, and whittle out the columns I know I don't need.

Again, I apologize that this doesn't answer the question "Why?" There's several good reasons, given in answers to similar questions. For me, a big reason is that a future reader of the statement isn't going to have to go look somewhere else to find out what columns are being returned. Sure they can copy the statement, and go run it in a different environment, to see the list. But if the statement has variable substitutions, bind variables, and the dots and double quotes and calls to mysql_real_escape_string (in the case of mysql_ interface), that's a bigger hassle than it needs to be. Sure, the code can be modified to echo out the SQL text before its executed, and the reader may need to do that. But someone just reviewing the code shouldn't have to do that. And having the list of columns and expressions being returned by the statement, in an order more appropriate than the ordinal position of the columns in the table, I think that just makes for more readable code. (If it's important for the column to be returned by the statement, then I think it's reasonable that the column name is shown in the query.)

(This was in terms of application code, statements that are going to be included in an application. For ad hoc queries and development and such, I use the SELECT c.* freely. But when a statement is going into an application, that * gets replaced.