5 ms·
This is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of reada
by hn_throwaway_99 6y ago
This is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of readability, I just wish there was more automated tool support for this format. The idea is to line up the first value from each clause. Visually it makes it extremely easy to "chunk" the statement by clause, e.g.:
SELECT a.foo, b.bar, g.zed
FROM alpha a
JOIN beta b ON a.id = b.alpha_id
LEFT JOIN gamma g ON b.id = g.beta_id
WHERE a.val > 1
AND b.col < 2
ORDER BY a.foo
- Hjfrf 6y agoDoes that still look ok if you're selecting 10+ columns with functions, or would you split out the first line situationally?
- hn_throwaway_99 6y agoAnother commenter showed how this works: SELECT a.foo , b.bar , g.zed FROM ... While the comma placement may seem weird, it makes this exactly identical to the "AND" or "OR" placement in WHERE clauses, and the primary benefit is that it's easy to comment out any column except the first.
- jolmg 6y ago> While the comma placement may seem weird It's not completely unconventional. Haskell is typically styled with that kind of comma usage, too. For example, [ 1 , 2 ] { foo = 1 , bar = 2 } Coincidentally, SQL and Haskell are the only languages I know that use `--` for comments.
- mulmen 6y agoWith the curly braces this makes a lot more sense. In SQL it offends my eyes (personal preference) but here it seems more clear.
- Wevah 6y agoFWIW, Lua and AppleScript (and HyperTalk!) use(d) `--` as well. (Edit: I think I misread “know” as “know of”; whoops.)
- jolmg 6y ago> I think I misread “know” as “know of”; whoops. Don't worry about it; you didn't misread. I wasn't aware of AppleScript and HyperTalk, and though I read a bit on Lua some years ago, I either forgot or never realized that it used `--` for comments.
- mulmen 6y agoIt's always easy to comment out any column except the first or last. Leading commas make it easy to comment out the last column, trailing commas make it easy to comment out the first. Personally I don't think that optimization is worth the price. Trailing commas look nicer visually so I prefer them.
- dspillett 6y agoI'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 agoMaintaining 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")
- 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.
- karmakaze 6y agoI find it more readable as SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo Usually only the conditions get deep and can also use extra indented parenthesized parts. reminder: don't use 'OUTER' it's pure noise
- AdrianB1 6y agoWe are doing something very similar, except we add extra indentation to the joins like you did for the second condition in the WHERE. This is because we strongly separate each block - SELECT, FROM, WHERE, <ORDER>, so everything that is in the FROM block is indented. Same for the SELECT if it so long it goes to a second or third row.
- sodapopcan 6y agoMy problem with formatting any code like this is that it can make diffs painful. I agree that this looks better but I would say only marginally so. And I really have no problems reading code that isn't lined up like this. I don't really have a high care level, though. I'm happy to go with the team on this one.
- hn_throwaway_99 6y agoI don't see why you think it would make diffs painful. If anything, in my experience it makes diffs easier because each chunk can be put on it's own, independent line so that if you change anything it is constrained to the relevant line.
- dragonwriter 6y agoIt makes diffs harder because maintaining the indentation rule (sometimes, depending on what is on other lines) requires changing every line of the query if you go from “INNER JOIN” (equally, outer/right/cross join) to “LEFT JOIN” (equally, full join).
- fiddlerwoaroof 6y agoNearly every diff tool has -w for this, though: main main annoyance with GitHub is that I can’t enable this as the default diff mode.
- sodapopcan 6y agoYep—`-w` is the default when I blame in my editor but ya, github is really the problem.
- majewsky 6y agoAt least GitHub now has a UI for enabling it. I remember the dark ages when you had to put ?w=1 on the URL like some sort of animal.
- weaksauce 6y agowhat about something like this? http://www.eslinstructor.net/vkbeautify/ http://www.eslinstructor.net/vkbeautify/
- saila 6y agoThis is the style I've settled on lately, where all the major keywords are left-aligned and the clauses are consistently indented. It uses a bit more vertical space, but I find it easier to read than any other formatting style I've seen (in the wild or produced by formatters). select a.foo, b.bar, g.zed from alpha a join beta b on b.alpha_id = a.id left join gamma g on g.beta_id = b.id where a.val > 1 and b.col < 2 order by a.foo It's really easy, for me anyway, to get an overview of the query with this style compared to styles that are more cramped or that are inconsistently wrapped/indented. For simpler queries, I think this is okay too, but only if the clauses easily fit on a single line: select ... from ... join ... where ...
- Icathian 6y ago10 years messing with data across 5 major flavors of sql and this is the format I've settled on and advocated for my team. It seems to flow the best and be easiest for people to get used to.
- u801e 6y agoI've found that using hanging indents and using prefixed binary operators, like you did here, is the most readable form and when adding additional clauses, makes for a readable diff.
- dbatten 6y agoMy company style guide explicitly specifies this. https://github.com/republicwireless-open/sql-style-guide https://github.com/republicwireless-open/sql-style-guide I think Mozilla's does as well.
- croes 6y agoIt's also easier if you want to comment out certain parts of your code during debugging.
- scottmcdot 6y agoOn this, I like to also use Where 1 = 1 And... And... Which makes it easy to comment out specific filters.
- ZeroClickOk 6y agoReference: https://www.sqlstyle.guide/ https://www.sqlstyle.guide/
- matwood 6y agoThis is close to what I’ve settled on for 20 years. I’ll also indent again if there are a lot of joins/clauses in the on.
- ianmcgowan 6y agohttps://www.sqlinform.com/ https://www.sqlinform.com/ is an excellent SQL formatting tool, where you can tweak the settings to whatever extent you like. I keep it around in notepad++ just for formatting SQL the way I like, even when writing/testing the SQL in VSCode or a DB tool.
- zephharben 6y agoJetbrains tools - specifically, PyCharm & Intellij IDEA - support this (gutter alignment) via the "Joe Celko" code style. With that enabled, you can autoformat you SQL to follow that convention. Oddly, DataGrip doesn't provide this option.
- gjvc 6y agoWould it be rude of me to ask you to open an item on https://youtrack.jetbrains.com/ https://youtrack.jetbrains.com/ for this please? I'm just a curious JetBrains DataGrip user.
- twodave 6y agoI haven't seen my formatting pattern listed, so here is the equivalent how I'd write it: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id LEFT JOIN gamma g ON b.id = g.beta_id AND a.id = g.alpha_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo;