from Hacker News

How I format SQL code

by szaboat on 1/30/20, 12:15 PM with 43 comments

by lancefisher on 1/30/20, 4:14 PM
I appreciate write-ups like this, but I really disagree with what seems to be the majority that SQL keywords should be uppercase. It’s one of the last uppercase holdovers from the old days. HTML used to be uppercase as well. Lowercase is objectively more readable, easier to type, and editors colorize keywords so they stand out. Uppercase is really not necessary in the 2020s. Check out Matt Mazur’s styleguide (linked in the post) for an alternative that endorses lowercase. He also has a contrasting style on where Boolean operators should go. https://github.com/mattm/sql-style-guide/blob/master/README....

by jolmg on 1/30/20, 4:14 PM

> God is merciful because AND_ is 4 characters, a good tab width, so WHERE conditions are to be lined up like (same for JOIN conditions)

  WHERE
          country = 'UAE'
      AND day >= DATE('2019-07-01')
      AND DAY_OF_WEEK(day) != 5
      AND scheduled_accuracy_meters <= 10*1000

It looks better when you use a tab-width of 2:

  WHERE country = 'UAE'
    AND day >= DATE('2019-07-01')
    AND DAY_OF_WEEK(day) != 5
    AND scheduled_accuracy_meters <= 10*1000

by dchess on 1/30/20, 7:21 PM
I don't see the benefit of putting table names on a different line than the keyword.
How is this:
```
  FROM
    tablename t
  INNER JOIN
    other_table ot
  ON
    t.id = ot.id
```
More readable than:
```
  FROM tablename t
  INNER JOIN other_table ot
    ON t.id = ot.id
```
I agree with a lot of these recommendations, but this one irks me. Also I'd love if someone could create a nice code-formatter for SQL like Python's Black.

by monkeycantype on 1/31/20, 12:34 AM

I also use his multi line format for boolean logic:

    select 'biscuit'
    where 
    (    
        (
            @alpha 
            < 
            pow(
                sin(   
                    radians(
                        @scheduled_lat - @actual_lat
                    ) 
                    / 2 
                )
                , 2
            )
        )
        and   
        @alpha > 0
    )

by flatfilefan on 1/30/20, 3:45 PM
Great style guide in my opinion. It is actually rather helpful to have those SQLs formatted neatly. As an analyst you have to write quite a few of them. So copy pasting and reusing is most helpful and boosts productivity. To make sure that you don’t make errors a clean layout for eyeballing is necessary. The same for bug fixing, should you have one planted still.
by truculent on 1/31/20, 9:06 AM
If it doesn’t come with an auto formatter it doesn’t matter. Making developers manually style their code is barbarism
by whynotmaybe on 1/30/20, 7:53 PM
Personaly, I put the comma before the column name :
```
  SELECT
     col1
     ,col2
     ,col3
```
It's easier for me to add a column or move it like this. Otherwise I have to search the comma when my query has only one column and I add one or when I add a column at the end
by merusame on 1/31/20, 6:54 AM
I struggle to find a beautifier doing something similar to this with indentation. I use quite a bit of plpgsql which makes it even more challenging. I have tried a few found in the www however none of them cut it. Any recommendations?

by arh68 on 1/30/20, 9:50 PM

This is my favorite guide yet!

My syntax, like others, is a little different (lowercase, 2 spaces, commas-first, bracket quotes, ons right under joins w/ joined table on LHS, left joins left-aligned): (this query isn't supposed to make sense)

    select
      u.id                   [user]
      , u.email              [email]
      , o.name               [office]
      , sum(t.id)            [# things]
    from main_tblusers_db u
    inner join tbloffices_db o
            on o.id = u.office_id
    inner join things_tbl t
            on t.user_id = u.id
    left  join example e
            on e.user_id = u.id
    where
      u.deleted is null
      and (
        u.active is not null
        or u.special = 1
      )
    group by
      u.id          -- the 1, 2 syntax is new to me!
      , u.email
      , o.name

by leblancfg on 1/30/20, 4:03 PM
The rest of this man's blog is also worth a visit. Great work, Marton!
P.S. Can I suggest you put your name somewhere in your header?
P.P.S. I see you, too, use 'self' when taking notes. Would you also be a Pythonista? :)
by wodenokoto on 1/30/20, 5:09 PM
Can anyone explain the logic / benefit of the group by recommendation?
by ninju on 1/30/20, 7:22 PM
It's a case of yet another standard (https://xkcd.com/927/)
The author recommends using upper-case for all keywords while Matt Mazur's SQL style guide, that is linked at the bottom of the article, recommends using lowercase for keywords :-)
by vladsanchez on 1/30/20, 5:54 PM
I've done it that way for the last 20 years, but I've never blogged/wrote about it. That's the difference.