SQL Statement indentation good practice [closed]


SQL Statement indentation good practice [closed]



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.




Why are positional queries bad?

1:



What does the term “Tuple” Mean in Relational Databases?
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..
How would I do a join of three tables with sql server

.
Understanding how JOIN works when 3 or more tables are involved. [SQL]

This is an example for those who do not use this type of writting SQL statement.


SQL: How to make sure any primary key in any table “belongs to” a known Account ID (also PK) at the top of the hierarchy?
Both contain an error of a missing comma.

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


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


SQL comment header examples
)
I found easier and more quick at the first example.


i want to get records using like keyword
Hope this example show you more my point of view..


2:


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..


3:


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      ) 


4:


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); 


5:


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  


6:


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   ) 


7:


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..


8:


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.. Edit: I believe that this is the 2014 location of the SQL formatter..


9:


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 ) 


10:


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...

.


11:


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 


12:


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                  ) 


13:


As most above have lined up the return column names, I find lining up tables names and conditions helps readability a lot..
SELECT      column1,      column2 FROM      table1 WHERE      column3 IN     (         SELECT TOP(1)              column4         FROM              table2 INNER JOIN              table3 ON table2.column1 = table3.column1     ) 
And for when join conditions get long..
SELECT     Column1,     Column2 FROM      Table1 JOIN      Table2 ON          Table1.Column3 = Table2.Column4 JOIN      Table3 ON          Table2.Column1 = Table3.Column1 and         Table2.ColumnX = @x and         Table3.ColumnY = @y WHERE     Condition1=xxx and     Condition2=yyy and     (         Condition3=aaa or         Condition4=bbb     ) 


14:


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) 
or like this:.
SELECT     column1,      column2 FROM      table1 WHERE      column3 IN (SELECT TOP(1) column4                  FROM table2                  INNER JOIN table3 ON table2.column1 = table3.column1) 


15:


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....


16:


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..


17:


This link is the best one I found.

http://www.sqlinform.com/free_online_sw.html.


18:


Here's my poke at this:.
select column1, column2     from table1     where (column3 in (         select top(1) column4             from table2             inner join table3                 on (table2.column1 = table3.column1)     )) ; 
  • Everything lowercase because it's easier to read lowercase characters (and we have code highlighting to emphasize keywords) also easier to type
  • Every restriction or option on a keyword (like the from on the select or the on on the join) is indented to show their dependance on the outward keyword
  • The closing bracket is at the same indenting level as the opening where
  • Use brackets for where- and on-clauses to increase readability
  • Have the semicolon closing the select-statement at the same indenting so multiple statements can be distinguished better (if you need a semicolon in your language like SAS PROC SQL does)
  • It's still quite compact and does not stretch all over the page


19:


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)"); 


20:


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                  ) 


21:


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..


22:


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..


23:


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.


24:


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


25:


That's how we would do it here:.
 select         COLUMN1,         COLUMN2,         case    when    COLUMN5 = 'X'                         and                         COLUMN6 = 'Y'                 then    'one'                 when    COLUMN5 in (                                 'AAA',                                 'BBB'                         )                 then    'two'                 else    'three'         end as COLUMN7 from         TABLE1 where         COLUMN2 in (                 select top(1)                         COLUMN4                 from                         TABLE2                         inner join                         TABLE3                                 on                                         TABLE2.COLUMN1 = TABLE3.COLUMN1                                         and                                         TABLE2.COLUMN2                                                 between                                                         TABLE3.COLUMN2                                                         and                                                         TABLE3.COLUMN3         ) 
Our idea is: keep sql keywords in lower case and put all changing (and therefore "more interesting") things like table or column names in upper case.

The code might look a bit "blown up" here, but it increases readability if you have complex queries with longer names (incl.

schema etc.) much longer than in this example.

And: indent all objects according to their "level"..



70 out of 100 based on 60 user ratings 1360 reviews