Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How do you indent SQL?

 
Timothy Chen Allen
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not to start a holy war or anything, but is there any published guideline on how to indent SQL so it is easier to read? Mainly I'm interested in SELECT statements, but the other stuff would be nice too.
I'm interested in the whole thing: indenting, whether keywords should be in upper or lower case, whether to indent with tabs or spaces...
I had started adopting a style like this:
SELECT foo, bar
FROM baz, bat
WHERE yuck = yam
But quickly ran into readability problems when there were many columns involved. Also I didn't take into account what to do with LEFT OUTER JOIN statements.
Thanks in advance.
 
HS Thomas
Ranch Hand
Posts: 3404
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer this way of [NOT] indenting; it's more readable.

The tricky part is to decide where to put brackets AND if you need them.
The above method is by far more readable than indenting heavily WITH brackets.
Indentations make one assume that the optimiser works in a particular way but usually this does not match exactly with the way SQL is written. Who knows, the optimisation changes daily depending on how volatile the data is. So most times I would prefer writing SQL, set at a time as shown above, and leave the rest to the optimiser/interpreter.

Sometimes you do neeed inner selects

Otherwise , indentations are more trouble than they are worth, IMHO.
(I haven't written SQL for a while so this may not be current practice but I am sure this method can be improved further..)HTH
regards
[ November 09, 2003: Message edited by: HS Thomas ]
 
Mathias Nilsson
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you do if there is TSQL involved with
IF..ELSE..END statements?
 
HS Thomas
Ranch Hand
Posts: 3404
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I haven't used T-SQL. Here is a forum that may help T-SQL Programming Part 1 - Defining Variables, and IF...ELSE logic
.
I have used PL/SQL which looks similar. I don't like having processing logic embedded within database calls or vice-versa. Doing this imposes limits on the scope for reusability. Still try and follow the same indentation rules I gave but refactor the SQL into another method if possible.
So how could you re-write this code ?

Hmmmm. While this example doesn't look messy with simple select count(*) statements it soon will with more complicated SQL. I would standardise on putting SQL in new methods / files that are easily traceable in the Development environment.Indented SQL looks like spaghetti after a while ; so personally I'd avoid indenting heavily.
regards
[ November 09, 2003: Message edited by: HS Thomas ]
 
Dolfandave Uyemura
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have done some T-SQL in a data migration project. I think the indentation was something like:
code:
--------------------------------------------------------------------------------
SELECT foo, bar
FROM baz, bat
WHERE yuck = yamma mamma AND
chuck = bananarama;
LEFT OUTER JOIN

SELECT foo, bar
FROM baz, bat
WHERE chuck = yamma mamma
AND (baz.yuck = bananarama OR bat.yuck = yamma mamma);
--------------------------------------------------------------------------------
Good Luck,
Dolfandave
 
Dolfandave Uyemura
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The last post didn't hold my indents. I have tried again below. Also, use a font like Courier New that is "monospaced font" so each character is exactly one space and always use spaces instead of tabbing. Tabs represent only ONE space so that can really throw you off.
code:
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Dolfandave

Code tags will help you.-- MdQ
[ November 15, 2003: Message edited by: Marilyn de Queiroz ]
 
Dolfandave Uyemura
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Still not holding form, here is a link I created and uploaded.

http://www.geocities.com/dave_uyemura/javaranch.html

Dolfandave
 
Timothy Lovern
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
for what it's worth:

Keywords get their own lines
columns and conditions get their own lines, with a level of indentation greater than the keywords.

This gets adjusted for complex queries or joins, but it is easy to read and very simple to add additional columns and or conditions to later.
[ November 15, 2003: Message edited by: Timothy Lovern ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic