5 ms·

I've taken to using a similar format too, though some seem to dislike it significantly. Other things I like for clarity and editing ease are prefix commas and l

I've taken to using a similar format too, though some seem to dislike it significantly. Other things I like for clarity and editing ease are prefix commas and lining up like parts, using something like your example: SELECT a.foo , b.bar , g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo or SELECT a.foo , b.bar , g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo I'm not consistent with the layout of my joining predicates - I go for whatever seems clearer given the current circumstances and that varies due to several factors (number of parts, length of column names and/or functions, ...). How sub-queries and instances of CASE are broken into lines and indented is something I also vary on.

thih9 6y ago
Maintaining alignment in these queries seems a pain. I'd prefer the regular, newlines and fixed indentation; e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo (bonus: "AND" got accidentally aligned with the end of "WHERE")
- akira2501 6y ago
  > Maintaining alignment in these queries seems a pain. I use tabs. SELECT t.foo, t.bar FROM a_table t
  - zikzak 6y ago
    This escalated quickly.
jolmg 6y ago
> though some seem to dislike it significantly I can see why. The indentation of the whole statement is not determined by the first line, but by the 6th on the first and the 8th on the second on a `JOIN` clause. It's really arbitrary, and when you have that statement between other code, it's going to be weird how the start of the statement is much more indented than its preceding code. I really dislike it, too. I prefer the use of indentation to signal what's inside another syntax structure. So, for example, I also dislike how you aligned `ON` and `AND` when the `AND` is inside the `ON` expression. It makes it seem like the two lines are on the same syntactic level. Here's how I do it: SELECT a.foo , b.bar , g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo You might also notice that I removed the padding you used to align the `=` signs. I dislike big changes where the only thing that changed for a line is the whitespace padding. It obscures the real change. It might not seem like a big thing when you only have 2 lines in alignment, but it's a real bother when reading a diff that does that for more lines. You have to compare between the - and + lines to find what really changed instead of the diff telling you outright.
- dspillett 6y ago
  > and when you have that statement between other code In many circumstances (chunks of SQL in SSIS steps and so forth) there is no code to be around. Within longer tracks of SQL I'll not let "LEFT OUTER JOIN" push the whole statement to the right, but will drop the table name to the next line and maintain the alignment from there on. > removed the padding you used to align the `=` signs. I dislike big changes where the only thing that changed for a line is the whitespace padding. It obscures the real change For modifying existing code, I will sometimes let the alignment slide to avoid unnecessary extra lines of change. Though a good visual diff tool will have the option to ignore white-space-only changes and I'm not concerned about patch sizes being a few lines bigger. To fix the alignment there may be a subsequent check-in that tidies up non-functional elements of the code. I'm happy to have an extra item in history in order to maintain readability over a stream of unaligned text. Something I've wanted for a long time in code editors is for tabs after the first non-whitespace character on a line to line up, that way such alignment could be managed without extra effort or extra lines in a diff. There would need to be some simple heuristics to control breaking the alignment (and these may need to vary between languages) and perhaps some way to control/override them (a directive in comments?) in edge cases.
arh68 6y ago
I like your second version. My own style, still evolving, is to write more lines and align further left select a.foo , b.bar , g.zed from alpha a inner join beta b on b.alpha_id = a.id and b.thing = a.another left join gamma g on g.beta_id = b.id where a.val > 1 and b.col < 2 order by a.foo
- mulmen 6y ago
  I like the idea in general, I have tried something similar before. But I've never understood the appeal of leading commas. It screws up your alignment and just looks messy.
  - ZeroClickOk 6y ago
    You can easily add remove columns like this, just removing the entire line. If you add the comma the traditional way, you will change 2 lines (the end of previous line with comma, and the new line). It's nice for maintenance and diff'ing
    - mulmen 6y ago
      That is only true of the first or last line. col1, col2, col3 col1 ,col2 ,col3 You can remove col2 from either of those examples and have valid syntax.
  - vericiab 6y ago
    Personally I like leading commas for the ergonomics rather than the aesthetics. When I'm developing or debugging a query the first column is typically the one I'm least likely to change. I tend to build up the query from there, so the last columns are the ones I'm most likely to change or to comment out. Plus I find it easiest to interpret the result set when columns that I'm using as a temporary reference are at the very end of the row. So for the way I work, I've found that with leading commas I don't have to do as much futzing about with commas. My job involves a lot of ELT pipelines though and the queries I'm writing are often to transform a client's data from whatever ill-conceived data model they've been using to a standard data model that we use for all clients. Those queries require a lot more "detective work" to get right than the queries that run against our standard data model. If I was just writing queries against the standard, I'm not sure I'd spend enough time developing/debugging to really notice any ergonomic benefit.

thih9 6y ago

Maintaining alignment in these queries seems a pain. I'd prefer the regular, newlines and fixed indentation; e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo (bonus: "AND" got accidentally aligned with the end of "WHERE")

akira2501 6y ago
> Maintaining alignment in these queries seems a pain. I use tabs. SELECT t.foo, t.bar FROM a_table t
- zikzak 6y ago
  This escalated quickly.

jolmg 6y ago

> though some seem to dislike it significantly I can see why. The indentation of the whole statement is not determined by the first line, but by the 6th on the first and the 8th on the second on a `JOIN` clause. It's really arbitrary, and when you have that statement between other code, it's going to be weird how the start of the statement is much more indented than its preceding code. I really dislike it, too. I prefer the use of indentation to signal what's inside another syntax structure. So, for example, I also dislike how you aligned `ON` and `AND` when the `AND` is inside the `ON` expression. It makes it seem like the two lines are on the same syntactic level. Here's how I do it: SELECT a.foo , b.bar , g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo You might also notice that I removed the padding you used to align the `=` signs. I dislike big changes where the only thing that changed for a line is the whitespace padding. It obscures the real change. It might not seem like a big thing when you only have 2 lines in alignment, but it's a real bother when reading a diff that does that for more lines. You have to compare between the - and + lines to find what really changed instead of the diff telling you outright.

dspillett 6y ago
> and when you have that statement between other code In many circumstances (chunks of SQL in SSIS steps and so forth) there is no code to be around. Within longer tracks of SQL I'll not let "LEFT OUTER JOIN" push the whole statement to the right, but will drop the table name to the next line and maintain the alignment from there on. > removed the padding you used to align the `=` signs. I dislike big changes where the only thing that changed for a line is the whitespace padding. It obscures the real change For modifying existing code, I will sometimes let the alignment slide to avoid unnecessary extra lines of change. Though a good visual diff tool will have the option to ignore white-space-only changes and I'm not concerned about patch sizes being a few lines bigger. To fix the alignment there may be a subsequent check-in that tidies up non-functional elements of the code. I'm happy to have an extra item in history in order to maintain readability over a stream of unaligned text. Something I've wanted for a long time in code editors is for tabs after the first non-whitespace character on a line to line up, that way such alignment could be managed without extra effort or extra lines in a diff. There would need to be some simple heuristics to control breaking the alignment (and these may need to vary between languages) and perhaps some way to control/override them (a directive in comments?) in edge cases.

arh68 6y ago

I like your second version. My own style, still evolving, is to write more lines and align further left select a.foo , b.bar , g.zed from alpha a inner join beta b on b.alpha_id = a.id and b.thing = a.another left join gamma g on g.beta_id = b.id where a.val > 1 and b.col < 2 order by a.foo

mulmen 6y ago
I like the idea in general, I have tried something similar before. But I've never understood the appeal of leading commas. It screws up your alignment and just looks messy.
- ZeroClickOk 6y ago
  You can easily add remove columns like this, just removing the entire line. If you add the comma the traditional way, you will change 2 lines (the end of previous line with comma, and the new line). It's nice for maintenance and diff'ing
  - mulmen 6y ago
    That is only true of the first or last line. col1, col2, col3 col1 ,col2 ,col3 You can remove col2 from either of those examples and have valid syntax.
- vericiab 6y ago
  Personally I like leading commas for the ergonomics rather than the aesthetics. When I'm developing or debugging a query the first column is typically the one I'm least likely to change. I tend to build up the query from there, so the last columns are the ones I'm most likely to change or to comment out. Plus I find it easiest to interpret the result set when columns that I'm using as a temporary reference are at the very end of the row. So for the way I work, I've found that with leading commas I don't have to do as much futzing about with commas. My job involves a lot of ELT pipelines though and the queries I'm writing are often to transform a client's data from whatever ill-conceived data model they've been using to a standard data model that we use for all clients. Those queries require a lot more "detective work" to get right than the queries that run against our standard data model. If I was just writing queries against the standard, I'm not sure I'd spend enough time developing/debugging to really notice any ergonomic benefit.