How I format SQL code • Hacker News Board

Re: How I format SQL code

#41

Post by eukaryote » Fri, Jan 31, 2020, 9:48 AM UTC

roelschroeven wrote: post #40

amp108 wrote: post #33

Earlier quoted context omitted.

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.

Same for me - I find the use of upper case keywords like this jarring in SQL. It seems quite old-school, like reading COBOL!

Re: How I format SQL code

#42

Post by arethuza » Fri, Jan 31, 2020, 9:58 AM UTC

eukaryote wrote: post #41

roelschroeven wrote: post #40

Earlier quoted context omitted.

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.

Same for me - I find the use of upper case keywords like this jarring in SQL. It seems quite old-school, like reading COBOL!

It's probably my repressed memories of FORTRAN that cause me to react so strongly to UPPERCASE.

Re: How I format SQL code

#43

Post by arh68 » Fri, Jan 31, 2020, 8:15 PM UTC

lazzlazzlazz wrote: post #36

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.

Well how far do you go with the river? Aligning with select means group by sticks out. Aligning with group by means left join sticks out. Aligning with left join means inner join sticks out.

EDIT: feel free to show me something better..

Re: How I format SQL code

#44

Post by lazzlazzlazz » Mon, Feb 03, 2020, 11:41 PM UTC

arh68 wrote: post #43

lazzlazzlazz wrote: post #36

Earlier quoted context omitted.

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.

Well how far do you go with the river? Aligning with select means group by sticks out. Aligning with group by means left join sticks out. Aligning with left join means inner join sticks out. EDIT: feel free to show me something better..

`GROUP BY` you align with the space between `GROUP` and `BY`.

`INNER JOIN` goes fully on the right side of the river.

The guide at https://www.sqlstyle.guide/ is almost perfect.