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

11–20 of 44 posts

Re: How I format SQL code

#11

Post by oarabbus_ »

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…

The first is actually much easier to read, and lower case is far superior.

Lowercase letters are read and comprehended faster: https://ux.stackexchange.com/questions/72622/how-easy-to-rea...

Additionally using casing when it has no meaning is an anti-pattern.

Re: How I format SQL code

#12

Post by oarabbus_ »

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…

I lowercase everything in my SQL.

Lowercase + indentation is the way to go. The only SQL that should be upper case is text in single quotes.

Re: How I format SQL code

#14

Post by jolmg »

oarabbus_ wrote: post #11
jolmg wrote: post #7

Earlier quoted context omitted.

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

The first is actually much easier to read, and lower case is far superior. Lowercase letters are read and comprehended faster: https://ux.stackexchange.com/questions/72622/how-easy-to-rea... Additionally using casing when it has no meaning is an anti-pattern.

That link talks in general. In general, I agree that lowercase is more readable.

> Additionally using casing when it has no meaning is an anti-pattern.

Why do you say that it has no meaning? This is about differentiating SQL keywords from table and column identifiers. That's the meaning.

> The first is actually much easier to read, and lower case is far superior.

Reading the query whole, sure, but are you seriously suggesting that you can skim for the identifiers faster in the all-lowercase one when there are no color hints?

Re: How I format SQL code

#15

Post by boublepop »

vladsanchez wrote: post #13

I've done it that way for the last 20 years, but I've never blogged/wrote about it. That's the difference.

There is absolutely no value for anyone in you sharing the fact that you don’t share your opinions on SQL style. Yet there is a lot of value in OP sharing his thoughts on style. That’s the difference.

Re: How I format SQL code

#16

Post by dchess »

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.

Re: How I format SQL code

#18

Post by philshem »

jolmg wrote: post #4

> 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

I don’t know if this will go over well, but how about the infamous 1=1?

    WHERE 1=1
      AND country = 'UAE'
      AND day >= DATE('2019-07-01')
      AND DAY_OF_WEEK(day) != 5
      AND scheduled_accuracy_meters
(More reading: https://stackoverflow.com/q/242822)

Re: How I format SQL code

#19

Post by whynotmaybe »

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

Re: How I format SQL code

#20

Post by CraftThatBlock »

philshem wrote: post #18
jolmg wrote: post #4

> 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

I don’t know if this will go over well, but how about the infamous 1=1? WHERE 1=1 AND country = 'UAE' AND day >= DATE('2019-07-01') AND DAY_OF_WEEK(day) != 5 AND scheduled_accuracy_meters (More reading: https://stackoverflow.com/q/242822 )

WHERE TRUE would also work, so would WHERE 1 I believe
Post reply on HN