Problems with SELECT *

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?

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