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

31–40 of 44 posts

Re: How I format SQL code

#31

Post by gwillz »

arh68 wrote: post #28

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…

Well this might be an interesting discussion to read for you. Take what you will from it.

https://gist.github.com/isaacs/357981

Re: How I format SQL code

#32

Post by strbean »

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…

Everything about SQL is a holdover from ancient programming languages. From crappy 'natural sounding' syntax constructs ("SELECT ... FROM ... WHERE ...") to outright inconsistencies ("INSERT INTO table (column1, column2) VALUES (1, 2)" vs "UPDATE table SET column1=1, column2=2").

It's a damn shame that the NoSQL movement turned out to just be NoRelational. RDBMS's could use a non-terrible query language.

Re: How I format SQL code

#33

Post by amp108 »

arethuza wrote: post #8
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…

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

Yeah, but how about this:

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

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 ... because a lot of time, when these nicely-formatted statements get parsed, the whitespace gets condensed, and when it gets spit out in an error message, I for one would like the all-caps keywords to be landmarks to direct my eye.

Re: How I format SQL code

#35

Post by lazzlazzlazz »

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…

Having written many many thousands of SQL queries, and having begun thinking that lower-casing keywords was better, I can tell you this is completely false.

You absolutely cannot rely on using your application of choice for editing queries: formatting them aggressively and allowing transmittable demarcations of query structure (like case) makes for the most portable, universally readable queries.

Given how many different systems we use these days to develop a single application, upper-casing is more necessary than ever in the 2020s.

Re: How I format SQL code

#36

Post by lazzlazzlazz »

arh68 wrote: post #28

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…

For the record, this syntax is horrific and almost unreadable to my eyes.

Multiple spaces after `LEFT` in `LEFT JOIN`? Just to stick with "river"-style alignment, yet your outer-level keywords (`SELECT`, `FROM`, etc.) aren't aligned?

It's difficult to understand why one would pick this format.

Re: How I format SQL code

#37

Post by available »

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…

What do you think about only capitalizing the first letter like this:

  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

Re: How I format SQL code

#38

Post by merusame »

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?

Re: How I format SQL code

#40

Post by roelschroeven »

amp108 wrote: post #33
arethuza wrote: post #8

Earlier quoted context omitted.

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

Yeah, but how about this: 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 vs.: SELECT region_fleet, CASE WHEN status = 'Delivered' THEN 'Delivered' ELSE 'Not Delivered' END AS status, DATE_TRUNC('week', day) AS week, COUNT(DISTI…

I still find the first one more readable.

Maybe it's just me, or maybe it's a matter of habit, but for me the changes from lowercase to uppercase and back are a kind of hurdle that make my brain pause a moment. The second example is much more cumbersome to me to read and parse.

Post reply on HN