Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How I format SQL code

bytepawn.com

Post reply on HN
Print view View original

1–10 of 44 posts

Re: How I format SQL code

#2

Post by flatfilefan »

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.

Re: How I format SQL code

#3

Post by leblancfg »

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? :)

Re: How I format SQL code

#4

Post by jolmg »

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

Re: How I format SQL code

#5

Post by lancefisher »

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....

Re: How I format SQL code

#6

Post by silveroriole »

lancefisher wrote: post #5

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…

“It's just as readable as uppercase SQL and you won't have to constantly be holding down a shift key.” If only there was some way not to hold shift, some kind of key that locks your case...!

Re: How I format SQL code

#7

Post by jolmg »

lancefisher wrote: post #5

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…

> editors colorize keywords so they stand out

Not when it's embedded as a string in another language, like when the query you want is not supported by the ORM.

> Lowercase is objectively more readable

No, and definitely not objectively. I generally don't capitalize my SQL, but I can't argue that using lowercase exclusively makes the SQL more readable. It definitely does help readability to differentiate SQL keywords from table and column names. Compare:

  select
      region_fleet,
      case when status = 'delivered' then 'delivered' else 'not delivered' end as status,
      date_trunc('week', day) as week,
      count(distinct row(day, so_number)) as num_orders,
      count(distinct case when scheduled_accuracy_meters
with

  SELECT
      region_fleet,
      CASE WHEN status = 'Delivered' THEN 'Delivered' ELSE 'Not Delivered' END AS status,
      DATE_TRUNC('week', day) AS week,
      COUNT(DISTINCT ROW(day, so_number)) AS num_orders,
      COUNT(DISTINCT CASE WHEN scheduled_accuracy_meters
It makes the column names stand out when you lack color hints. You can quickly skim to see what data is involved in a query without visually parsing the expressions.

Re: How I format SQL code

#8

Post by arethuza »

jolmg wrote: post #7
lancefisher wrote: post #5

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…

> editors colorize keywords so they stand out Not when it's embedded as a string in another language, like when the query you want is not supported by the ORM. > Lowercase is objectively more readable No, and definitely not objectively. I generally don't capitalize my SQL, but I can't argue that using lowercase exclusively makes the SQL more readable. It definitely does help readability to differentiate SQL keywords…

For what it is worth, I find the first of those (i.e. the lowercase one) much easier to read.

Re: How I format SQL code

#10

Post by oarabbus_ »

wodenokoto wrote: post #9

Can anyone explain the logic / benefit of the group by recommendation?

It's useful to put the grouping columns so you can say `group by 1,2,3,4,5` instead of `group by 1,2,6,7,9`.

Implementing production queries, you tend to write out the full column names, but for 95% of your SQL this is a boon to the analyst or data scientist.

Post reply on HN