JavaRanch Home    
 
This page:         last edited 01 August 2013         What's Changed?         Edit

Column lists in SQL statements   

Always Use a Column List

Whenever you perform a command, be it select or an insert, you MUST ALWAYS include a list of the columns wanted/affected.

Why... because there is no guarantee about the order of columns. Just because you describe a table (or use your database viewer) and the columns are shown that way, it doesn't mean they always will.

One poster was having a curious issue that his columns were appearing in a different order on different viewers. This is an odd case but it neatly shows the issue.

Even if you argue, “but I always use the same JDBC version that isn't going to change”, imagine this:

You have written an insert statement into students for new admissions.

  Insert into students(name, date of entry, course)
This all works .

Next month they realise, we need to update the database when a student leaves, simple add a not mandatory (null) column, and update this when they leave. They do an impact analysis and this value isn't required for new students so your new student code isn't touched. But adding a new student starts to fail with a JDBC error "not enough values, error 500". due to the database now having four columns but you are only providing 3, and it doesn't know which 3.

This would also affect any selects, it is possible the creation of a column could affect the order of the columns, so your select of column 3 could return any random value.

Sod's law being what it is, it wouldn't throw an exception, just give you invalid calculations.

Specifying the column to select can have additional benefits: firstly, it clearly documents which columns of a table are actually used by the code, which can help future maintenance of the database.

Secondly, it helps improve performance. By selecting only the columns which are actually processed, data of the unneeded columns are not uselessly transferred from the database to the client. A less obvious advantage is that the database itself might be able to process such a query faster: if all columns being selected are part of the same index, the database might be able to retrieve requested values from that index, saving some IO operations needed to retrieve the data from the underlying table. This obviously depends on the database being used.


SqlBestPractices

JavaRanchContact us — Copyright © 1998-2014 Paul Wheaton