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?

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

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

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