Skip to main content

My SQL Formatting Guide

2022-08-10

Introduction

My current opinions on SQL formatting. Will update as preferences change.

1. Lowercase keywords

IntelliSense on modern IDEs makes capitalization obsolete.

select pk from orders --good
SELECT pk FROM orders --bad/unnecessary
sElEcT pk fRoM orders --insane

2. Indentation

Clauses should always be at the same level of indentation. Single-item clauses can appear on one line, while clauses with multiple items should be listed on subsequent lines. For example:

select
    id
    ,amount
    ,date
from orders

Because the select clause is comprised of multiple items, so each appears indented on its own line. Contrast that with the from clause, which is only selecting from a singular table. Thus a single-line from clause is acceptable.

3. Joins

I like to put joins on a singular line, including the on clause, like so:

from
    orders as o
    left join customers as c on o.customer_id = c.customer_id

4. Consistent naming

A pk/fk should be consistently named across tables to make joins obvious.

5. snake_case for field names

snake_case is easier to read than camelCase. Also, spell things out rather than using cryptic acronyms. For example:

select order_net_amount_with_tax from orders --good
select orderNetAmountWithTax from orders --bad

6. Prefer CTEs over sub-queries

CTEs organize logic into modular, re-usable packages. The ability to name a CTE makes it easier for future developers to understand what you were going for than reading a cryptic sub-query.

7. Good CTEs reduce the need for comments

Construct small, atomic CTEs with clear names, and comments are surprisingly rarely necessary.

8. Never use * in a final select

While I use select * during data exploration, it should never be used in production code. Putting in a wildcard in a final select allows for new fields to flow through your SQL if they are added to upstream tables, which can break things. Always explicitely list out the fields you want to return.

9. Leading commas

A hotly-contested matter of personal preference, I've come to embrace putting commas before fields for two reasons:

  1. I find that I am more likely to remove / comment out a nth item in a select clause than the 1st. This is easier to do with leading commas.

  2. Commas are an annoying source of syntax errors. By lining all your commas up vertically, it is easier to see what is missing. I find this makes debugging quicker/easier.

Compare the following:

SELECT
    pk,
    name,
    age,
    birthdate,
FROM
    customers

vs

SELECT
    pk
    ,name
    ,age
    ,birthdate
FROM
    customers

Did you even notice that the first example has is incorrect and will error?

10. Avoid quotation marks

SQL allows the use of quotation marks to include spaces in table or column names. Only a mad man would do that. Stick to camel_case.

11. Avoid abbreviations

I alluded to this in #5, but spelling things out for clarity is more important than saving horizontal space on the screen. Clarity should be top priority when writing SQL. For example:

  • count_orders is better than cnt_ordrs

  • customer_billing_address is better than cust_bill_addr

12. Uniform suffixes

Picked some of these up from www.sqlstyle.guide, while others are work conventions:

  • _id - for unique identifiers such as a primary or foreign key

  • _total - for sum() fields

  • _ds - for date fields

  • _ts - for timestamp fields

13. Write boolean flag fields as questions

I like this one because it makes for more readable queries later. If you start a boolean field with is_ or has_, then using it to filter (a common pattern) results in a very human-readable code snippet. For example:

select order_id
from orders
where has_first_order

14. between > and while in > multiple or clauses

An example to illustrate the point:

select name
from people
where
    age >= 20
    and age < 30
    and (height = 72
        or height = 74
        or height = 76)

vs

select name
from people
where
    age between 20 and 30
    and height in (72, 74, 76)