SQL "Beautifying"

Read PL/SQL Beautifier at Tim Hall's blog. I like the idea of a built in SQL, and PL/SQL, formatter for Oracle. If Oracle creates a built-in formatter, I'd like it to be extensible. Have a default format and give the user (us) the ability to tweak it to our liking. Once the "beautifier" comes out and it is integrated into 3rd party tools, you'll have one location to tweak your SQL and PL/SQL formatting style. That sounds good to me. While you're at it, throw in an asserter so we can just say DBMS_BEAUTIFIER.assert_is_formatted( v_sql_or_plsql_clob ).


However, I'm writing to discuss "good" and "bad" SQL formats. I've found that many of the suggested standards are either poor for readability or poor for maintainability.

First some bad:
1) Aligning the keywords

SELECT DUMMY
 FROM DUAL
WHERE 1 = 1
  AND 2 = 2

Why is it bad? On its own, it doesn't make a difference. If you try and follow that format in PL/SQL, it becomes both hard to maintain and hard to read. Also, try writing fluent SQL while trying to figure out how many spaces you need to enter before starting your keyword. Formats should be as close to natural writing as possible

2) Commas before columns

SELECT
 DUMMY d1
 , DUMMY d2
 , DUMMY d3
FROM DUAL

This is annoying because the comma isn't the most important thing about each row: THE COLUMN IS. This format is used to "help" people remember commas after each non-terminal column. If a programmer can remember to put a semi-colon after every single line of code they make, they can remember to put a comma after non-terminal columns. The most important information should be first


And some good:
1) Alias everything in the from list and use those aliases for EVERY column reference.
This adds both readability and maintainability:

• Easier to determine from where columns are coming - readability


• May be more self-documenting - readability


• Easier to switch which tables or views are referenced - maintainability


• Allows the addition of columns without breaking the query - maintainability

SELECT
  ORDER_ID,
  CUSTOMER,
  SALES_TOTAL,
  PRODUCT,
  COMMENTS
FROM ORDERS, ORDER_DETAILS
WHERE ORDER_ID = ORD_ORDER_ID

Where are sales_total and comments coming from?
What if I want to use my ORDERS_SUMMARY view instead of ORDERS?
What if I want to add a comments column to my ORDER_DETAILS table?

Aliasing everything answers all these questions clearly:

SELECT
  o.ORDER_ID,
  o.CUSTOMER,
  od.SALES_TOTAL,
  od.PRODUCT,
  od.COMMENTS
FROM ORDERS o, ORDER_DETAILS od
WHERE o.ORDER_ID = od.ORD_ORDER_ID  

2. Put each item of the select or from list on its own line when it exceed 2 elements
One of the most common changes for a query is adding information or changing from where information is coming. By putting each select and from element on its own line, you not only make it easier to read, but you make it easier to add, delete, and remove from and select elements. It also clears up any confusion between columns used in a function call vs columns in the select list.

3. Put each parameter of a function call on its own line when it exceeds 2 elements
This one is more a rule of thumb (ROT) and should be broken as you see fit. For instance, with decodes, I generally put the item being decoded on its own line and then put pairs of elements on their own line and then the default on its own line. For example:

DECODE(
flag,
'M', 'MONTHLY',
'D', 'DAILY',
'W', 'WEEKLY',
'OTHER'
)

This ROT is especially useful when you begin nesting functions. For example:

SUBSTR( my_str, DECODE( INSTR( my_str, ',' ), 0, LENGTH( my_str ), INSTR( my_str, ',' ) ) )

vs

SUBSTR(
  my_str,
  DECODE( 
    INSTR( my_str, ',' ),
    0, LENGTH( my_str ),
    INSTR( my_str, ',' )
  )
)