3 ms·

Yes! I write SQL this way. I grant the comma thing is more a matter of taste, but aligning clauses vertically and maximizing rivers is super useful to me and it

• new_guy 5y ago
  That formatting makes my eyes bleed.
• RussianCow 5y ago
  Do you format your code this way by hand or does your IDE help you? Also, I can only imagine how noisy your diffs would be when you make changes and have to indent everything.
  • extr 5y ago
    I'll use PyCharm's SQL formatter sometimes to massage spaghetti code that someone else wrote, but mostly I just write it this way by hand. I will say my use case is probably different than others, I'm a Data Scientist so I write a ton of throwaway SQL and stuff that will only be seen by me. If I'm dealing with an existing SQL codebase or production stuff, definitely try to keep it more easily diff-able (like left justifying the keywords, really only caring about rivers in the join/where clauses), or just follow whatever patterns are already there.
• marcosdumay 5y ago
  This looks nearly the same, but it's much easier to write and generates less spurious diffs: select a , b , count(*) as count from table join table2 on table.key1 = table2.key1 and table.key2 = table2.key2 and DATEADD('day, 1, table.key3) = table2.key3 where cond1 and cond2 group by a, b order by count desc The largest difference is that the equal operators aren't aligned anymore, but on my opinion, aligning them is really not worth the cost.
  • extr 5y ago
    Nice changes, I like it! I will say I really like aligning the join clauses, but I'll try writing things this way for a little while and see if it sticks.
    • janto 5y ago
      The only thing I require from an SQL formatter is to make typos and boolean mistakes super easy to spot, especially as it grows in size. At first glance it might seem strange, but I like indentation on even the first term of an AND. SELECT a, b, count(*) as count FROM table JOIN table2 ON table.key1 = table2.key1 AND table.key2 = table2.key2 AND -- 1 day apart DATEADD('day', 1, table.key3) = table2.key3 WHERE cond1 AND cond2 AND ( cond3 OR cond4 ) GROUP BY (a, b) ORDER BY count desc, a asc LIMIT 1000 ; Yes, that's a lot of whitespace, but it helps for complex queries.
      • marcosdumay 5y ago
        Well, there's an inherent conflict between making individual queries easier to read and making your entire code easier to read. How much whitespace you need is completely dependent on what exactly you are writing.
• Orou 5y ago
  > it forever mystifies me that it isn't standard I can give a few thoughts on why here: - it doesn't work for collaborative efforts unless everyone is using the same linter or IDE with the same configuration. Not friendly to edits outside of that ecosystem (e.g. open source). - there are other ways of writing SQL (e.g. dbt style guide) which are still very readable while also being much faster to write by hand without the need for an auto-formatter. - some of these patterns such as the from and join indentations have nothing to do with readability and are purely stylistic. Nothing wrong with that, but there's nothing objectively better about this pattern - it just comes down to how you're used to reading SQL.
• roenxi 5y ago
  > and it forever mystifies me that it isn't standard I can take a stab at justifying that - commas are used to form lists (eg, 1, 2, 3) and text (like "order by count") are supposed to be left justified. You may be right that there are good reasons to format SQL while ignoring the commas and rules for writing English text. I personally think you are right. But if most people believed that then there'd be enough support to rewrite SQL with a decent modern syntax. Say allowing training commas before a from. So the existence of SQL is, in a weird way, evidence that your view is uncommon.