7 ms·

How I format SQL code

60 points by szaboat 7y ago

ninju 7y ago
It's a case of yet another standard (https://xkcd.com/927/ https://xkcd.com/927/) The author recommends using upper-case for all keywords while Matt Mazur's SQL style guide, that is linked at the bottom of the article, recommends using lowercase for keywords :-)
flatfilefan 7y ago
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.
leblancfg 7y ago
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? :)
jolmg 7y ago
> 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 <= 10*1000 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 <= 10*1000
- philshem 7y ago
  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 <= 10*1000 (More reading: https://stackoverflow.com/q/242822 https://stackoverflow.com/q/242822)
  - CraftThatBlock 7y ago
    WHERE TRUE would also work, so would WHERE 1 I believe
lancefisher 7y ago
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.md https://github.com/mattm/sql-style-guide/blob/master/README....
- silveroriole 7y ago
  “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...!
- jolmg 7y ago
  > 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 <= 500 then row(day, so_number) else null end) as num_accurate, avg(scheduled_accuracy_meters) as scheduled_accuracy_meters from deliveries where ... group by 1, 2, 3 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 <= 500 THEN ROW(day, so_number) ELSE NULL END) AS num_accurate, AVG(scheduled_accuracy_meters) AS scheduled_accuracy_meters FROM deliveries WHERE ... GROUP BY 1, 2, 3 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.
wodenokoto 7y ago
Can anyone explain the logic / benefit of the group by recommendation?
- oarabbus_ 7y ago
  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.
vladsanchez 7y ago
I've done it that way for the last 20 years, but I've never blogged/wrote about it. That's the difference.
- boublepop 7y ago
  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.
dchess 7y ago
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.
- Macha 7y ago
  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)
whynotmaybe 7y ago
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
- BossingAround 7y ago
  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 ...
  - whynotmaybe 7y ago
    That would definitely be a game changer... but I'm not sure I might be ready for that !
arh68 7y ago
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
- gwillz 7y ago
  Well this might be an interesting discussion to read for you. Take what you will from it. https://gist.github.com/isaacs/357981 https://gist.github.com/isaacs/357981
- lazzlazzlazz 7y ago
  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.
  - arh68 7y ago
    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..
    - lazzlazzlazz 7y ago
      `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/ https://www.sqlstyle.guide/ is almost perfect.
monkeycantype 7y ago
I also use his multi line format for boolean logic: select 'biscuit' where ( ( @alpha < pow( sin( radians( @scheduled_lat - @actual_lat ) / 2 ) , 2 ) ) and @alpha > 0 )
merusame 7y ago
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?
truculent 7y ago
If it doesn’t come with an auto formatter it doesn’t matter. Making developers manually style their code is barbarism

60 points by szaboat 7y ago

It's a case of yet another standard (https://xkcd.com/927/ https://xkcd.com/927/) The author recommends using upper-case for all keywords while Matt Mazur's SQL style guide, that is linked at the bottom of the article, recommends using lowercase for keywords :-)

flatfilefan 7y ago

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.

leblancfg 7y ago

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

jolmg 7y ago

> 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 <= 10*1000 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 <= 10*1000

philshem 7y ago
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 <= 10*1000 (More reading: https://stackoverflow.com/q/242822 https://stackoverflow.com/q/242822)
- CraftThatBlock 7y ago
  WHERE TRUE would also work, so would WHERE 1 I believe

lancefisher 7y ago

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.md https://github.com/mattm/sql-style-guide/blob/master/README....

silveroriole 7y ago
“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...!
jolmg 7y ago
> 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 <= 500 then row(day, so_number) else null end) as num_accurate, avg(scheduled_accuracy_meters) as scheduled_accuracy_meters from deliveries where ... group by 1, 2, 3 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 <= 500 THEN ROW(day, so_number) ELSE NULL END) AS num_accurate, AVG(scheduled_accuracy_meters) AS scheduled_accuracy_meters FROM deliveries WHERE ... GROUP BY 1, 2, 3 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.

wodenokoto 7y ago

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

oarabbus_ 7y ago
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.

vladsanchez 7y ago

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

boublepop 7y ago
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.

dchess 7y ago

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.

Macha 7y ago
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)

whynotmaybe 7y ago

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

BossingAround 7y ago
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 ...
- whynotmaybe 7y ago
  That would definitely be a game changer... but I'm not sure I might be ready for that !

arh68 7y ago

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

gwillz 7y ago
Well this might be an interesting discussion to read for you. Take what you will from it. https://gist.github.com/isaacs/357981 https://gist.github.com/isaacs/357981
lazzlazzlazz 7y ago
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.
- arh68 7y ago
  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..
  - lazzlazzlazz 7y ago
    `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/ https://www.sqlstyle.guide/ is almost perfect.

monkeycantype 7y ago

I also use his multi line format for boolean logic: select 'biscuit' where ( ( @alpha < pow( sin( radians( @scheduled_lat - @actual_lat ) / 2 ) , 2 ) ) and @alpha > 0 )

merusame 7y ago

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?

truculent 7y ago

If it doesn’t come with an auto formatter it doesn’t matter. Making developers manually style their code is barbarism