Originally posted on StackOverflow.
When it comes to dealing with complex SQL, it has been my experience that using vertical space liberally is your best friend, particularly once you move to systems with more than a single join in most of your queries.
I’ve written a code standard that I have been using for about 5 years.
SELECT column1, column2 FROM table1 WHERE column3 IN ( SELECT TOP(1) column4 FROM table2 INNER JOIN table3 ON table2.column1 = table3.column1 )
On more complex queries it becomes more obvious how this is useful:
SELECT Column1, Column2, Function1 ( Column1, Column2 ) as Function1, CASE WHEN Column1 = 1 THEN a ELSE B END as Case1 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.column12 = t2.column21 WHERE ( FilterClause1 AND FilterClause2 ) OR ( FilterClause3 AND FilterClause4 )
Any code style guide needs to have a goal; this one has the specific goal of making it really easy to read the column list and to discover clause correlations, particularly in insert-select statements. The basic principles of the guide are:
- Each clause should be indented
- Within a clause, a list of conditions or other “phrases” should be indented
- This includes fields or values, which should each have their own line
- When a field or value has a complex definition, it should be indented so as to make its structure obvious