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

Question

SQL seems to be the most neglected language when it comes to formatting nicely and readably... And as SQL statements can be incredibly detailed and complex, it makes it extremely hard to work with. But I find that when I try to format my SQL code in the best way possible I'm sometimes unsure of how to do it. I know the standards for Java, C#, Python, etc.... but when it comes to SQL I haven't seen too many guidelines or accepted practices. What are tips/rules for formatting SQL so that it's clear, legible, and logical? Can you give example code to illustrate? What have you found to be the most standard, accepted way of formatting SQL?

Greenaway

Not a new subject. Check out stackoverflow.com/questions/118288/sql-coding-style-guide, and others with the coding-style and sql tags. – Cryptomeria

There are standards for Java and C# formatting? Spread the word, it might finally stop the endless holy wars about brace style and tab-vs-space indenting. – Loaning

@Stephen P - true, but at least there are known styles. I've been reading a chapter in Code Complete recently, and there are controversies, but he basically sets out all the commonly used styles and then it's up to you/your workplace to choose. But I haven't even seen discussion about SQL, everyone seems to just follow their own gut feelings – Greenaway

Answer 1

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

Hest

Similar to me, but I'd have the INNER JOIN (without INNER!) aligned with tables (it's not a major clause) and condition in line. Either way, this general layout is one of the cleaner ones I've seen. – Ataliah

True... maybe I'll try indenting the JOINs and see how I like that. I don't like just including the criteria on the same line though, because it can often be rather long - and I hate scrolling left/right. I prefer one criteria on each line. – Hest

I'd start to indent the criteria if it was long, but keep it on one line if it's short: in particular, "JOIN table1 ON table1.table1_id = table2.table1_id" is the most common case by a long way. Some postgresql bias here since that can be written "JOIN table1 USING (table1_id)" which would be silly to split. – Shashaban

Answer 2

Maybe this s "cheating" ;) - but I just found an amazing site that does this for you!

http://poorsql.com

And the options are fully customizable

Greenaway

Awesome tool, and free to boot! – Jaddan

Answer 3

I generally follow this type of syntax for MSSQL Server

SELECT statemenets

SELECT //optionally specify top or distinct
  Field1,
  Field2,
  CASE WHEN (1 = 1) THEN
      "1"
    ELSE
      "2"
  END AS Field3,
  ...
FROM Table1 t1
INNER JOIN Table2 t2
  ON t2.field1 = t1.field1  //I always reference the joined tables field name first
LEFT OUTER JOIN Table3 t3
  ON (t3.field1 = t1.field1
    AND t3.field2 = t2.field2)  //I specify and with a new line and tabbed in
    OR                         // I specify or(s) on thier own line this way you can distinguish from the two conditionals that need to be met
    (t3.field1 = t2.field1
    AND t3.field2 = t1.field2)
WHERE
   (t1.Field1 = 'foo'
   AND t1.field2 = 'bar')
   OR
   (t2.Field1 = 'foo'
   AND t1.field2 = 'bar')

Derived Tables in a Select

Select
  Field1,
  Field2,
  ...
FROM (Select
          Field1,
          Field2,
          Field3)
        FROM Table1
        WHERE
          Field1 = '1') t1

Update Statements

UPDATE Table1
  SET
    Field1 = 1,
    Field2 = 2,
    Field3 = 3
WHERE
  (Field1 = 2
  AND Field3 = 2)
  OR 
  (Field3 = 1)

Insert Statements

INSERT INTO Table1
  (Field1,
   Field2,
   Field3,
   ...)
VALUES
  (1,
   2,
   3,
   ...)

If Statements

IF (some condition) BEGIN
END ELSE BEGIN
END

Procedures

CREATE PROCEDURE Foo (
  Bar INT,
  Foo VARCHAR(20)
) AS
BEGIN
  //Your Code Here
END

Cusick

I find that to be incredibly verbose. Most databases don't need "INNER" OR "OUTER" in the join syntax, and a line return for each join criteria is a waste of space when there's ~80 characters across the screen. Using that format is risky for dynamic SQL, unless you concatenate each line with opening & terminating clauses - on Oracle at least - because the space can be interpreted as intential characters. – Cookstove

I'm glad that you mentioned referencing the joined table's field name first. Without parentheses though, your AND/OR logic is a bit ambiguous. – Hest

@OMG Ponies. Yeah I know you dont need the Inner and Outer you can just say JOIN instead of INNER JOIN and LEFT JOIN instead of LEFT OUTER JOIN. Its just a habbit I formed. – Cusick

@Tom H. The without paranthesis is a typo. I meant to add parenthesis. Thanks for catching that – Cusick

I like this style a lot. Although it wastes some space on common lines, it ensures that the structure is almost always consistent even when you have to cope with long clauses. I don't mind vertical scrolling and consistency makes for easy reading. – Mauriciomaurie

Answer 4

I use the following rules:

• Always uppercase sql reserved words (SELECT, FROM, WHERE, HAVING, AND, OR, DISTINCT etc.)

Ugly:
select height,width,age from person where width = 20
Tidy:
SELECT height, width, age FROM person WHERE (width = 20)

• Lowercase all table names. Never use camelcase (donkeyWrench) in table names (you will shoot yourself in the head if you create the queries by hand).

• Always use parentheses at WHERE and HAVING clauses. Use space between operators.

Ugly:
... where width=20 and height>20
Tidy:
WHERE (width = 20) AND (height > 20)

• Use aliases for tablenames.

`SELECT * FROM donkeywrench dw ...`

• Use readable, tablename related primary key fields. I always start keys and primary keys with 'id_'.

tablename: donkeywrench, primary key: id_donkeywrench

• Mark where the query originated from. While reading logs you can easily track down where the problem occured.

/*Called from donkeykong.php, line 22*/ SELECT * FROM donkeywrench dw ...

• If the query is loooong

- Always leave the operator (AND, OR) at the end of the line
- Use parentheses!

Example:

/*Executed from xyz.php*/
SELECT 
p.height, p.width, p.age,
pd.hastel, pd.hasmobile

FROM 
person p
LEFT JOIN personaldata pd ON p.id_person = pd.id_person
LEFT JOIN relatives r ON pd.id_person = r.id_person

WHERE 
( p.width = 20 ) AND 
( (p.height > 20) AND (p.height < 15) ) AND
( pd.hastel)

ORDER BY 
p.age, p.height

Corespondent

I disagree with the parentheses around WHERE conditions. When operator precedence is clear, I find it distracting. It's like writing the expression ((x * y) + (s * t)) / z instead of (x * y + s * t) / z. When there are fewer parentheses, it's easier to see how they match up. Of course, this is a matter of preference. – Alkyl