views:

3220

answers:

21

Hi,

What is the accepted practice for indenting SQL statements? For example, consider the following SQL statement:

SELECT column1, column2
FROM table1
WHERE column3 IN
(
SELECT TOP(1) column4
FROM table2
INNER JOIN table3
ON table2.column1 = table3.column1
)

How should this be indented? Many thanks.

+2  A: 

This is my personal method. Depending on the length of the join condition I sometimes indent it on the line below.

SELECT
  column1,
  column2
FROM
  table1
WHERE
  column3 IN ( 
    SELECT TOP(1)
      column4
    FROM
      table2
      INNER JOIN table3 ON table2.column1 = table3.column1
  )


SELECT
  column1,
  column2
FROM
  table1
WHERE
  column3 IN ( 
    SELECT TOP(1)
      column4
    FROM
      table2
      INNER JOIN table3
        ON table2.column1 = table3.column1 -- for long ones
  )
Jason Lepack
This (Jason's way) is how I do it. Clause keywords in their own column, on their own line. Clause components indented. Comma-separated or boolean-separated lists are one line per item. Open parentheses on the same line (not on their own lines).
Pistos
+14  A: 
SELECT column1, column2
FROM table
WHERE column3 IN (
    SELECT TOP(1) column4
    FROM table2
    INNER JOIN table3 ON table2.column1 = table3.column1
)

This is pretty short and easy to read. I'd make adjustments if there were more columns selected or more join conditions.

Bill the Lizard
That's the same indenting scheme I like to use.
Justice
A lot of text books use this or something very similar. I like to keep it simple. :)
Bill the Lizard
I would tend to go for this style personally, except I would drop the opening ( on to a new line.
MagicAndi
I wouldn't change it if I found the opening ( on its own line. :)
Bill the Lizard
Yes, a new line for every major keyword, nested sections indented. I don't like all the scrolling up and down when long lists of column names are on separate rows. I want to see as much as possible on one screen.
DOK
+8  A: 

Not sure there is an accepted practice, but here's now how I'd do it:

SELECT 
    column1, 
    column2 
FROM 
    table1 
WHERE 
    column3 IN 
    ( 
     SELECT TOP(1) 
         column4 
     FROM 
         table2 
         INNER JOIN 
         table3 
             ON table2.column1 = table3.column1 
    )
Codewerks
+14  A: 
SELECT column1
     , column2
FROM table1
WHERE column3 IN
(
 SELECT TOP(1) column4
 FROM table2
 INNER JOIN table3
 ON table2.column1 = table3.column1
)

I like to have all "," in front, this way I never search them when an error at line X from the SQL editor.


This is an example for those who do not use this type of writting SQL statement. Both contain an error of a missing comma.

SELECT sdcolumn123
 , dscolumn234
 , sdcolumn343
 , ffcolumn434
 , sdcolumn543
 , bvcolumn645
  vccolumn754
 , cccolumn834
 , vvcolumn954
 , cvcolumn104
FROM table1
WHERE column3 IN
(
 ...
)

SELECT sdcolumn123, dscolumn234, asdcolumn345, dscolumn456, ascolumn554, gfcolumn645 sdcolumn754, fdcolumn845, sdcolumn954, fdcolumn1054
FROM table1
WHERE column3 IN
(
 ...
)

I found easier and more quick at the first example. Hope this example show you more my point of view.

Daok
Yes pre-comma looks silly but its so much easier to manage in the long run.
cfeduke
What does that mean, "this way I never search them"?
Codewerks
With the leading comma, you always know where they are and don't have to go looking for them.
Ken Gentle
sometime you search where you are missing the comma... it's clear when they are in the front. The day you will have an error in an SQL statement because of comma you will think of me ;)
Daok
Very clever! Since I pre-comma I never need to care about comma error anymore. Nice one.
Pokus
+1 for the pre-comma trick. With such a short column list I completely forgot to mention it.
Bill the Lizard
Very interesting programming practice, commas in front! I will try this out.
Pistos
to me, this adversely impacts readability, like the old joke goes... "At my <fill in the blank>, they taught us not to leave out the commas.."
Charles Bretana
At first I didn't like it but once you get it, you understand how it's more readable.
Daok
I think it would be a better comparison if you had newlines in the second example: most people (that I've met/spoken to about this) prefer the commas at the end of the line.
Ed Woodcock
If you have a reasonably "vertical" style, end-of-line commas are perfectly scannable, particularly if you make a habit of using a column label for every column.
Mike Burton
A: 

What I usually do is,

print("SELECT column1, column2
       FROM table1
       WHERE column3 IN (SELECT TOP(1) column4
                         FROM table2 INNER JOIN 
                              table3 ON table2.column1 = table3.column1)");
+2  A: 

Of course, this comes down to personal preference. And if in a team setting, it's something that should be agreed upon among the members for consistency's sake. But this would be my preference:

SELECT column1, column2
FROM   table1
WHERE  column3 IN(SELECT     TOP(1) column4
                  FROM       table2
                  INNER JOIN table3 ON
                             table2.column1 = table3.column1
                 )
Bullines
+1  A: 

I would format like this:

SELECT
    column1, 
    column2
FROM 
    table1
WHERE 
    column3 IN (SELECT TOP(1) 
                    column4 
                FROM 
                    table2 
                    INNER JOIN table3 ON table2.column1 = table3.column1)
Mitch Wheat
A: 

I don't know if there's a standard but I like to do it this way;

SELECT column1, column2
  FROM table1
WHERE column3 IN
(
    SELECT TOP(1) column4
      FROM table2
    INNER JOIN table3
      ON table2.column1 = table3.column1
)

because I can read and analyze the SQL better.

nmiranda
+6  A: 

I like to have "rivers" of white space in the code. It makes it a little easier to scan.

SELECT column1,
       column2
  FROM table1
 WHERE column3 IN (SELECT column4
                     FROM table2
                     JOIN table3
                       ON table2.column1 = table3.column1);
jalbert
+1  A: 

This is my normal preference:

....SELECT column1
........,column2
....FROM table1
....WHERE column3 IN (
........SELECT TOP(1) column4
........FROM table2
........INNER JOIN table3
............ON table2.column1 = table3.column1
....)

Although stackoverflow messes up the formatting with an extra leading space, so I put in some periods so you can see the actual formatting...

Cade Roux
A: 

This is a matter of taste.

This is my preference.

SELECT 
  column1
 ,column2
FROM
  table1
WHERE column3 IN (
                 SELECT TOP(1) column4
                 FROM 
                   table2
                   INNER JOIN table3
                 ON table2.column1 = table3.column1
                 )
JosephStyons
+2  A: 

I like to have the different parts of my query line up vertically. I tend to use a tab size of 8 spaces for SQL which seems to work well.

SELECT  column1, 
        column2
FROM    table1
WHERE   column3 IN
(
        SELECT TOP(1) column4
        FROM    table2
        INNER JOIN table3
        ON      table2.column1  = table3.column1
)
Jack Ryan
+1  A: 

Well, of course it depends on the query.

For simple queries, a highly formal indentation scheme is just more trouble than it's worth and can actually make the code less readable, not more. But as complexity grows you need to start being more careful with how you structure the statement, to make sure it will be readable again later.

Joel Coehoorn
A: 

Yeah, this is pretty subjective...But here's my 2 cents:

SELECT
   Column1,
   Column2
FROM Table1
WHERE 
   Column3 IN (
      SELECT Column4
      FROM Table2
      JOIN Table3 ON
         Table2.Column1 = Table3.Column1
   )

But, really, I'd probably rewrite it without the IN:

SELECT
   Column1,
   Column2
FROM Table1
JOIN Table2 ON
   Table1.Column3 = Table2.Column4
JOIN Table3 ON
   Table2.Column1 = Table3.Column1

Basically, my rules are:

  • Capitalize Keywords
  • Columns go on individual lines, but SELECT modifiers (SELECT TOP 100, SELECT DISTINCT, etc.) or single columns (SELECT 1, SELECT Id, SELECT *, etc.) go on same line
  • Join conditions indented underneath JOIN clause
  • Use JOIN for INNER JOIN (since it's the common one), and fully specify others (LEFT OUTER JOIN, FULL OUTER JOIN, etc.)
  • Open parens on same line, close paren on separate line. If you have an alias, the alias goes with close paren.
Mark Brackett
Capitalize Keywords doesn't work on PostGresql, they will require to be in "" because it's case sensitive. Watch out the type of DB.
Daok
The docs don't seem to mention that quirk - and even have captialized keywords in the example: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
Mark Brackett
+2  A: 

SQL formatting is an area where there is a great deal of variance and disagreement... But fwiw, I like to focus on readability and think that whatever you do, consistently conforming to any rules that reduce readability is, as the old cliche goes, a "foolish consistency" ( "Foolish consistency is a hobgoblin for simple minds" )

So, instead of calling them rules, here are some guidelines. For each Major clause in a SQL statement (Select, Insert, Delete, From, Where, Having, Group BY, Order By, ... I may be missing a few) should be EASILY identifiable. So I generally indent them at the highest level, all even with each other. Then within each clause, I indent the next logical sub structure evenly... and so on.. But I feel free to (and often do) change the pattern if in any individual case it would be more readable to do so... Complex Case statements are a good example. Because anything that requires horizontal scrolling reduces readability enormously, I often write complex (nested) Case expressions on multiple lines. When I do, I try to keep the beginning of such a statement hanging indent based on it's logical place in the SQL statement, and indent the rest of the statement lines a few characters furthur...

SQL Database code has been around for a long time, since before computers had lower case, so there is a historical preference for upper casing keywords, but I prefer readability over tradition... (and every tool I use color codes the key words now anyway)

I also would use Table aliases to reduce the amount of text the eye has to scan in order to grok the structure of the query, as long as the aliases do not create confusion. In a query with less than 3 or 4 tables, Single character aliases are fine, I often use first letter of the table if all ther tables start with a different letter... again, whatever most contributes to readability. Finally, if your database supports it, many of the keywords are optional, (like "Inner", "Outer", "As" for aliases, etc.) "Into" (from Insert Into) is optional on Sql Server - but not on Oracle) So be careful about using this if your code needs to be platform independant...

Your example, I would write as:

Select column1, column2
From table1 T1
Where column3 In (Select Top(1) column4
                  From table2 T2
                     Join table3 T3
                         On T2.column1 = T3.column1)

Or

Select column1, column2
From table1 T1
Where column3 In 
     (Select Top(1) column4
      From table2 T2
         Join table3 T3
            On T2.column1 = T3.column1)

If there many more columns on the select clause, I would indent the second and subsequent lines... I generally do NOT adhere to any strict (one column per row) kind of rule as scrolling veritcally is almost as bad for readability as scrolling horizontally is, especially if only the first ten columns of the screen have any text in them)

Select column1, column2, Col3, Col4, column5,
    column6, Column7, isNull(Column8, 'FedEx') Shipper,
    Case Upper(Column9) 
       When 'EAST'  Then 'JFK'
       When 'SOUTH' Then 'ATL'
       When 'WEST'  Then 'LAX'
       When 'NORTH' Then 'CHI' End HubPoint
From table1 T1
Where column3 In 
     (Select Top(1) column4
      From table2 T2
         Join table3 T3
            On T2.column1 = T3.column1)

Format the code in whatever manner makes it the most readable...

Charles Bretana
+5  A: 

I like jalbert's form of lining up the keywords on their right. I'd also add that I like the ANDs and ORs on the left (some people put them on the right.) In addition, I like to line up my equals signs when possible.


SELECT column1, 
       column2  
  FROM table1, table2 
 WHERE table1.column1 = table2.column4 
   AND table1.col5    = "hi" 
    OR table2.myfield = 678 
Slapout
My personal preference too - I find it helps enormously when quickly scanning code if the keywords are lined up precisely where one expects them to be
Cruachan
+1  A: 

I've written a code standard for our shop that is biased in the extreme towards readability/"discoverability" (the latter being primarily useful in insert-select statements):

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
    )

Once you move to systems with more than a single join in most of your queries, it has been my experience that using vertical space liberally is your best friend with complex SQL.

Mike Burton
+4  A: 

If you have a lengthy SQL statement that you'd like to reformat without all the typing and tabbing, you can slap it into this website and get a nicely formatted result. You can experiment with various formats to see which makes your text the most readable.

DOK
Cool link. Not sure I'd use it myself, but something nice to bookmark.
Pistos
+1  A: 

I've just put it through my SQL prettifier and it came out like this....

SELECT column1, column2
FROM table1
WHERE column3 IN
(
SELECT TOP(1) column4
    FROM table2
            INNER JOIN table3
            ON table2.column1 = table3.column1
)

http://extras.sqlservercentral.com/prettifier/prettifier.aspx

.....But I haven't worked out a way of getting colours into StackOverflow.

A: 

If there a way/tool of automatically indenting SQL code like most IDEs do for Java/C# in Eclipse or Visual studio?

Tami
Tami, you shouldn't ask another question as an answer to an existing question; people will down-vote it. A quick search on StackOverflow.com shows that there are a number of existing questions around SQL formatting tools:http://stackoverflow.com/questions/2856813/free-sql-formatter-tooland http://stackoverflow.com/questions/401928/sql-formatter-for-sql-management-studioandhttp://stackoverflow.com/questions/627643/sql-formatting-tool
MagicAndi
Ok MagicAndi sorry for that. Will do it next time (-:
Tami
+1  A: 

Example indenting a very very very complex SQL:

SELECT 
    produtos_cesta.cod_produtos_cesta, 
    produtos.nome_pequeno,
    tab_contagem.cont,
    produtos_cesta.sku, 
    produtos_kits.sku_r AS sku_kit, 
    sku_final = CASE
        WHEN produtos_kits.sku_r IS NOT NULL THEN produtos_kits.sku_r
        ELSE produtos_cesta.sku
    END,
    estoque = CASE
        WHEN produtos2.estoque IS NOT NULL THEN produtos2.estoque
        ELSE produtos.estoque
    END,
    produtos_cesta.unidades as unidades1, 
    unidades_x_quantidade = CASE
        WHEN produtos.cod_produtos_kits_tipo = 1 THEN CAST(produtos_cesta.quantidade * (produtos_cesta.unidades / tab_contagem.cont) * produtos_kits.quantidade AS int)
        ELSE CAST(produtos_cesta.quantidade * produtos_cesta.unidades AS int)
    END,
    unidades = CASE
        WHEN produtos.cod_produtos_kits_tipo = 1 THEN produtos_cesta.unidades / tab_contagem.cont * produtos_kits.quantidade
        ELSE produtos_cesta.unidades
    END,
    unidades_parent = produtos_cesta.unidades,
    produtos_cesta.quantidade,
    produtos.controla_estoque, 
    produtos.status
FROM 
    produtos_cesta 
INNER JOIN produtos 
    ON (produtos_cesta.sku = produtos.sku) 
INNER JOIN produtos_pacotes 
    ON (produtos_cesta.sku = produtos_pacotes.sku) 
INNER JOIN (
    SELECT 
        produtos_cesta.cod_produtos_cesta,
        cont = SUM(
            CASE
                WHEN produtos_kits.quantidade IS NOT NULL THEN produtos_kits.quantidade
                ELSE 1
            END
        )
    FROM 
        produtos_cesta 
    LEFT JOIN produtos_kits 
        ON (produtos_cesta.sku = produtos_kits.sku) 
    LEFT JOIN produtos 
        ON (produtos_cesta.sku = produtos.sku) 
    WHERE 
        shopper_id = '" + mscsShopperId + @"' 
    GROUP BY 
        produtos_cesta.cod_produtos_cesta, 
        produtos_cesta.sku, 
        produtos_cesta.unidades 
) 
AS tab_contagem
    ON (produtos_cesta.cod_produtos_cesta = tab_contagem.cod_produtos_cesta)
LEFT JOIN produtos_kits 
    ON (produtos.sku = produtos_kits.sku) 
LEFT JOIN produtos as produtos2
    ON (produtos_kits.sku_r = produtos2.sku) 
WHERE 
    shopper_id = '" + mscsShopperId + @"' 
GROUP BY 
    produtos_cesta.cod_produtos_cesta, 
    tab_contagem.cont,
    produtos_cesta.sku, 
    produtos_kits.sku_r, 
    produtos.cod_produtos_kits_tipo, 
    produtos2.estoque,
    produtos.controla_estoque, 
    produtos.estoque, 
    produtos.status, 
    produtos.nome_pequeno, 
    produtos_cesta.unidades, 
    produtos_cesta.quantidade,
    produtos_kits.quantidade
ORDER BY 
    produtos_cesta.sku, 
    produtos_cesta.unidades DESC
José Américo Antoine Jr