Thursday, February 3, 2011

SQL formatting style

I have written considerably more SQL than most programmers have. And after you've written and maintained several hundred line queries, it becomes obvious that SQL is a programming language like any other. Which means that indentation helps comprehension for all the same reasons that it would in any other language. (I'm continually puzzled by programmers who fail to grasp this, and try to put SQL statements on one line.) But what indentation style should you use?

Here are the principles that lead to my current chosen style.

  1. Indentation should be in the range of 2-4 spaces. Research indicates that people can read and understand code better with an indent in that range than one which is larger or smaller than that.
  2. When one thing depends on another, indentation should be increased. This makes the core statements obvious.
  3. When easy, line things up.
  4. In any list of things, it should be possible to extend the list by pasting new lines on without changing others. In a language like SQL where you can't have trailing commas, that means putting the comma before the line.
  5. If you wrap, then operators belong at the front of lines. This makes the logical flow easy to see at a glance. (I got this one from Perl Best Practices.)
  6. SQL is usually case insensitive, so use underscores in field names to avoid ambiguous parses of things like ExpertsExchange and ExpertSexChange.

And here is an example of what this looks like in practice.

, x.baz
WHEN s.blat = 'Hello'
THEN 'Greeting'
WHEN s.blat = 'Goodbye'
THEN 'Farewell'
ELSE 'Unknown'
END as blat_type
FROM table1 s
JOIN table2 t
ON s.some_id = t.some_id
AND s.some_type = 'wanted type here'
FROM yet_another_table yat
WHERE yat.reason = 'Subquery demonstration'
) AS x
ON t.another_id = x.another_id
WHERE s.blat = 'some'
AND t.blot = 'condition'

If you've never thought about how to format SQL this style is likely to have a few surprises. But try maintaining a lot of SQL that has been formatted in this way, and you should find that it works out quite well.


Anonymous said...

You can even include comments in your SQL code. Though, nobody ever seems to do that.

Ben Tilly said...

I tend to comment lightly in general. But I've certainly left lots of comments in SQL when appropriate.

lytri said...

My formatting looks a lot like yours does, with the addition that my CASE blocks have the THEN lines indented once more, as they're dependent on the WHEN lines (much like JOIN - ON). Thanks!

Ashley said...

Strangely enough I came to a nearly identical style after writing little SQL but lots of JS where the trailing commas become so painful compared to Perl. It was obvious that the SQL would benefit from the same approach.