Problems with SELECT *

From: Curtis \ <>
Date: 12 Mar 2003 18:40:53 GMT
Message-ID: <b4nuvl$>

Hi all,

I was having a discussion with someone over his SQL snippet in a program:

   SELECT * FROM emails

I do a lot of heavy database work and I hate seeing "SELECT *". As a general rule, when trying to write maintainable code, it's a good idea to be very specific about what you will allow. For example, consider the following SQL statement:

   SELECT userid, email, contest FROM emails

That SQL statement has a variety of benefits:

  1. It's immediately obvious to the maintenance programmer exactly what is being requested.
  2. It's more efficient than selecting a bunch of columns you don't need, if you have more columns in the table than you're actually using.
  3. If you access fetched data in a particular order (fetching the fields into an array, for example), you don't have to worry about columns in the database being reordered.
  4. If access the data by name (in a hash or dictionary) you will understand faster why "emails.userid" failed when the field was renamed to "user_id".
  5. If someone removes a column from the database, you get the same benefit as mentioned in the fourth point.

I've now been asked where I can find an online article which details similar pitfalls (including the "select * from foo" example). I've had trouble finding anything and I was wondering if anyone could suggest some?

Ovid Received on Wed Mar 12 2003 - 19:40:53 CET

Original text of this message