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

21–30 of 44 posts

Re: How I format SQL code

#21

Post by BossingAround »

whynotmaybe wrote: post #19

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

I know this is a question of style, but wow that looks ugly.

The point about ease of adding a new column is absolutely valid.

The best answer to it, subjectively and IMHO, is on the language level, e.g. making it legal to end the statement with a comma:

SELECT col1, col2, col3, from ...

Re: How I format SQL code

#22

Post by whynotmaybe »

BossingAround wrote: post #21
whynotmaybe wrote: post #19

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

I know this is a question of style, but wow that looks ugly. The point about ease of adding a new column is absolutely valid. The best answer to it, subjectively and IM H O, is on the language level, e.g. making it legal to end the statement with a comma: SELECT col1, col2, col3, from ...

That would definitely be a game changer... but I'm not sure I might be ready for that !

Re: How I format SQL code

#23

Post by lancefisher »

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…

Some editors do syntax highlight "language injections" or SQL embedded as a string. e.g. Rider by JetBrains: https://blog.jetbrains.com/dotnet/2018/10/29/sql-inside-c-st...

Re: How I format SQL code

#24

Post by oarabbus_ »

jolmg wrote: post #14
oarabbus_ wrote: post #11

Earlier quoted context omitted.

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

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

One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes.

On the other hand, (in python for example) SOME_FUNCTION() and some_function() both have meaning. They do not refer to the same thing.

>but are you seriously suggesting that you can skim for the identifiers faster in the all-lowercase one when there are no color hints?

Yes. Especially since my eyes don't get stuck on the massive blobs of ALL CAPS YELLING TEXT in the select.

Re: How I format SQL code

#25

Post by jolmg »

oarabbus_ wrote: post #24
jolmg wrote: post #14

Earlier quoted context omitted.

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

>Why do you say that it has no meaning? This is about differentiating SQL keywords from table and column identifiers. One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes. On the other hand, (in python for example) SOME_FUNCTION() and some_function() both have meaning. They do not refer to the same thing. >but are…

> One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes.

That the language doesn't enforce a meaning doesn't mean that we can't add meaning to the casing. This is like how in multiple languages it's convention to write constant variables in all-caps even though few languages enforce it.

Re: How I format SQL code

#26

Post by oarabbus_ »

jolmg wrote: post #25
oarabbus_ wrote: post #24

Earlier quoted context omitted.

>Why do you say that it has no meaning? This is about differentiating SQL keywords from table and column identifiers. One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes. On the other hand, (in python for example) SOME_FUNCTION() and some_function() both have meaning. They do not refer to the same thing. >but are…

> One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes. That the language doesn't enforce a meaning doesn't mean that we can't add meaning to the casing. This is like how in multiple languages it's convention to write constant variables in all-caps even though few languages enforce it.

That's a fantastic example because an all-caps constant actually does have a different meaning than the same word spelled in lowercase, and all the more reason to not capitalize keywords in SQL (Structured Query Language, being an acronym, is proper to capitalize)

Re: How I format SQL code

#27

Post by mmgutz »

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 2nd shouts at me from all angles, maybe a side effect from being hounded by netiquette police in forums.

Re: How I format SQL code

#28

Post by arh68 »

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

Re: How I format SQL code

#29

Post by jolmg »

oarabbus_ wrote: post #26
jolmg wrote: post #25

Earlier quoted context omitted.

> One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes. That the language doesn't enforce a meaning doesn't mean that we can't add meaning to the casing. This is like how in multiple languages it's convention to write constant variables in all-caps even though few languages enforce it.

That's a fantastic example because an all-caps constant actually does have a different meaning than the same word spelled in lowercase, and all the more reason to not capitalize keywords in SQL (Structured Query Language, being an acronym, is proper to capitalize)

Could you fill out the following sentence, please?

When an identifier is written in all-caps, it generally _____ that it's a constant.

I feel like you're just, in bad faith, refusing to acknowledge that the word "meaning" doesn't have to be in any way related to language enforcement. Are you trying to argue for the sake of arguing?

Re: How I format SQL code

#30

Post by Macha »

dchess wrote: post #16

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.

In the join case, it makes your diffs nicer when joining multiple tables

    FROM foo
    INNER JOIN
        other_table using (other_table_id)
to:

    FROM foo
    INNER JOIN
    +  foo_bars using (foo_id),
       other_table using (other_table_id)
Post reply on HN