SQL Statement Indentation Good Practice

SQL Statement indentation good practice

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.

SQL formatting standards

I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.

The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.

Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT on 
    JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT on 
    ST.SourceTableID = SJT.SourceTableID
where
        ST.SourceTableID = X
    and JT.ColumnName3 = Y
    and JT.Column3 = SJT.Column4

One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.

Algorithm for neatly indenting SQL statements (Python implementation would be nice)

You can try sqlparse. It's a Python module that provides simple SQL formatting. A online demo is available here.

How to make sure that my SQL code is not a scary mess

You could try checking out Joe Celko's book SQL Programming Style. I'm sure that there are a lot of people who disagree with his style, but it's a good start.

Some of my own "rules"

• SQL keywords are always all upper-case
• Table names are "proper" case, while columns and variables are all lower-case
• Each "major" clause in a statement is at the start of a line
• JOIN and WHERE criteria appear beneath and are indented and aligned
• Nested items are indented further
• I use aliases for all tables and views

For example:

SELECT
    column_1,
    column_2,
    CASE
        WHEN column_5 = 'Blah' THEN 1
        WHEN column_6 = 'Blah' THEN 2
        ELSE 3
    END AS column_alias
FROM
    My_Table MT
INNER JOIN My_Other_Table MOT ON
    MOT.column_1 = MT.column_1
WHERE
    MT.column_2 = 'Some Value' AND
    (
        MT.column_3 = 'Some other value' OR
        MT.column_4 = 'Some other value'
    )

Can VIM autoindent SQL?

By installing the python module sqlparse, which makes the sqlformat command available in your terminal.

pip install sqlparse

from vim you can use

:%!sqlformat --reindent --keywords upper --identifiers lower -

in order to attach a shortcut ,pt I added following configuration to my .vimrc config file:

autocmd FileType sql call SqlFormatter()
augroup end
function SqlFormatter()
    set noai
    " set mappings...
    map ,pt  :%!sqlformat --reindent --keywords upper --identifiers lower -<CR>
endfunction

You can customize sqlformat a bit. See

sqlformat --help

Python SQL query string formatting

Sorry for posting to such an old thread -- but as someone who also shares a passion for pythonic 'best', I thought I'd share our solution.

The solution is to build SQL statements using python's String Literal Concatenation (http://docs.python.org/), which could be qualified a somewhere between Option 2 and Option 4

Code Sample:

sql = ("SELECT field1, field2, field3, field4 "
       "FROM table "
       "WHERE condition1=1 "
       "AND condition2=2;")

Works as well with f-strings:

fields = "field1, field2, field3, field4"
table = "table"
conditions = "condition1=1 AND condition2=2"

sql = (f"SELECT {fields} "
       f"FROM {table} "
       f"WHERE {conditions};")

Pros:

1. It retains the pythonic 'well tabulated' format, but does not add extraneous space characters (which pollutes logging).
2. It avoids the backslash continuation ugliness of Option 4, which makes it difficult to add statements (not to mention white-space blindness).
3. And further, it's really simple to expand the statement in VIM (just position the cursor to the insert point, and press SHIFT-O to open a new line).

What SQL coding standard do you follow?

Wouldn't call it coding standard - more like coding style

SELECT
    T1.col1,
    T1.col2,
    T2.col3
FROM
    table1 T1
    INNER JOIN ON Table2 T2 ON T1.ID = T2.ID
WHERE
    T1.col1 = 'xxx'
    AND T2.Col3 = 'yyy'

• capitalize reserved words
• main keywords on new line
• can't get used to commas before columns
• always use short meaningful table aliases
• prefix views with v
• prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
• don't prefix tables
• table names singular

What indenting style do you use in SQL Server stored procedures?

SELECT      T1.Field1,
            T1.Field2,
            T2.Field1 As Field 3
FROM        Table1 AS T1
LEFT JOIN   Table2 AS T2
ON          T1.Field1 = T2.Field7
WHERE       T1.Field9 = 5
AND         T2.Field1 < 900
ORDER BY    T2.Field1 DESC

INSERT INTO Table1 (
            Field1,
            Filed2,
            Field3 )
VALUES (    'Field1',
            'Field2',
            'Field3' )

UPDATE      Table1
SET         Field1      = SomeValue,
            Field2      = AnotherValue,
            FIeld134567 = A ThirdValue
WHERE       Field9      = A Final Value

I find that I dont necessarily has a set indentation length and instead I try to indent based on the length of the field names and values. I like my left margins to line up along any given vertical plane and I like my Evaluators (such as equal signs) to line up. I always have any command term on a different vertical plane than its accompanying values and fields. I also tend to try to make the space between my SELECT command and the Field list equal in length to the space used by a SELECT DISTINCT Field or INSERT INTO Table.

But in the end, all that is just my preferences. I like neat looking code.

How to format long SQL queries according to PEP8

What about

cursor.execute("""SELECT pivot_id
                    FROM aud_qty
                   WHERE hshake1 is NULL
                     AND ((strftime('%s', DATETIME('now')) -
                         strftime('%s', sent_to_pivot)) / (60)) > 30;
               """)

? Using """ or ''' you get the same behaviour as a very long string but you can use newlines just fine. And your database won't mind them either.

Is there a good reason to use upper case for SQL keywords?

It's just a matter of style, probably originating in the days when editors didn't do code colouring.

I used to prefer all upper case, but I'm now leaning towards all lower.

Either way, be consistent.

---

Related Topics