I was reading Josh Berkus last blog post and was intrigued by his last post Writing Maintainable Queries Part I.
He is right that lots has been said about coding standards in other languages and even right out holy wars have been launched on the subject, but as far as SQL goes, not quite enough has been said on the subject for us to have a great war to end all wars.
I was also happy to see that we agreed with all his points except his first one. Yes I felt dissed, and thought hmm if someone as important as Josh thinks our aliases should be very descriptive and we should use the table name rather than the alias where possible, surely there must be something wrong with me for not believing in this fundamental philosophy.
In the rest of this excerpt I shall make fun of Josh and also set forth some of our own SQL Coding guidelines. Hopefully Josh won't take too much offense at this small jibe.
Getting back to Josh. Imagine a world filled with Josh Berkus wannabies taking his advice to its logical conclusion and what you get is this.
SELECT foot_from_left.foot AS the_foot_I_stuck_in_mouth,
foot_from_right.foot AS the_foot_from_the_right, mouth AS the_mouth_I_stuck_my_left_foot_in
FROM foot AS foot_from_left INNER JOIN foot AS foot_from_right ON foot_from_right.person = foot_from_left.person
INNER JOIN mouth ON ( mouth.person = foot_from_right.person)
WHERE mouth.location = foot_from_left.location AND foot_from_left.foot_position ='L' AND foot_from_right.foot_position = 'R'
I found a great cartoon depicting this somewhere, but don't seem to find this at the moment.
Yes we would be those disrespectful people who would be guilty of writing that statement as follows because those extra characters are not only more to type, but they actually get in the way of targetting join mistakes and where is the indentation. INDENTATION is the most important thing:
SELECT fl.foot AS fl_foot, fr.foot AS fr_foot, mouth.mouth
FROM foot AS fl INNER JOIN foot AS fr ON fl.person = fr.person
INNER JOIN mouth ON (mouth.person = fr.person)
WHERE mouth.location = fl.location AND fl.foot_position = 'L' AND fr.foot_position = 'R'
Indenting, uppercasing SQL keywords, and using AS are the most important conventions we try to hold to.
Of course this is no offense to Josh - just a little nit-pick. Kind of reminds me of working on PostGIS, and one day to my disbelief I discovered the philosophies of 2 people I have great respect for look like this.
Paul
if ( lots_of_spaces_and_lots_of_under_scores && !camels ){
yeah();
}
Mark
if(!one) scream();
and of course all I could think -- What barbarians! How could I even share the same planet with these people?
When all is said and done, probably the most important thing when working on a team, is that everyone grudgingly agrees to follow the same sane standard and the code created done by 20 programmers looks like it was done by the same person.