Better SQL: don't use SELECT *

By Greg Jorgensen, principal consultant, PDXperts LLC

SQL programmers often use SELECT * (retrieve all columns from a table or relation). Except for database utility programs, typical applications never need to use SELECT *. Four good reasons:

  1. SELECT * breaks abstraction and data hiding, and couples your code to the physical database structure. Details of the database schema should stay hidden from higher levels of code. SELECT * forces the code that uses the result set to know the actual column names. If your database supports stored procedures you can use them to hide the database schema. Otherwise simple wrapper functions or classes accomplish the same thing.

  2. A person reading the SQL statement can't tell which columns the client code uses. Writing SELECT * saves a few seconds when coding, but when the schema changes and you (or someone else) has to check the code to see if the change affects the code, the job becomes much harder and more error-prone because of the SELECT *.

  3. SELECT * makes debugging and testing harder and less certain. SELECT * won't fail or signal an error if the schema changes. Instead code farther along will fail when it tries to reference a column not in the result set. If the SELECT lists the columns by name it will fail the first time it executes against an incompatible schema, rather than passing the problem downstream where you may not discover it right away.

  4. SELECT * may retrieve more columns that the client code needs. If you or someone else adds columns (maybe even big text or blob columns), SELECT * will fetch those columns even though the client code doesn't need them. This wastes memory on the database server and increases network traffic.

In a properly-designed relational schema, adding a column to a table or relation won't affect any existing code (unless the code refers to columns by numeric index, perish the thought). SELECT * deprives you and other programmers using the database of this important relational feature.

When testing and debugging from the SQL command line SELECT * helps you work faster, but don't transfer the SELECT * shortcut to production code. A database utility that explores the schema can legitimately use SELECT *, but otherwise just don't do it.