Formatting SQL

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:

  1. Each clause should be indented
  2. Within a clause, a list of conditions or other “phrases” should be indented
  3. This includes fields or values, which should each have their own line
  4. When a field or value has a complex definition, it should be indented so as to make its structure obvious