Best practices for writing SQL queries • Hacker News Board

Re: Best practices for writing SQL queries

#121

Post by markmark » Sat, Apr 10, 2021, 9:45 PM UTC

dspillett wrote: post #17

GordonS wrote: post #10

Earlier quoted context omitted.

It seems to be a matter of personal preference, but I've never liked single-character aliases myself, and never understood why so many seem to.

Lazy typing: t is shorter than tableWithTheDataIWantIn I prefer descriptive table and other object names, and abbreviate them in aliases within queries (though usually not to single letters).

It's not just about lazy typing it's about removing unnecessary clutter from large queries that makes things harder to read. In the author/books example, repeating the words author and books a dozen times doesn't convey any information that a and b don't, but clutters up the query making it harder to see the useful parts.

Re: Best practices for writing SQL queries

#122

Post by matwood » Sat, Apr 10, 2021, 9:45 PM UTC

hn_throwaway_99 wrote: post #8

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…

This 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.

Re: Best practices for writing SQL queries

#123

Post by mulmen » Sat, Apr 10, 2021, 9:48 PM UTC

magicalhippo wrote: post #115

mulmen wrote: post #100

Earlier quoted context omitted.

My editor has tab completion, it's not like you have to type every character. I prefer it for readability but it's definitely debatable.

Readability is weird heh, I'm exactly the opposite. For me long lines become a blur, so better to have a short alias.

I know what you mean. I think this is really a matter of readability vs comprehension. I want all the context on one line so I can understand it, even if that does technically make it harder to read that line.

This is especially a problem in huge queries.

Re: Best practices for writing SQL queries

#124

Post by majewsky » Sat, Apr 10, 2021, 9:54 PM UTC

colonwqbang wrote: post #101

> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’s index. This implies that WHERE style join can't use indices. I can understand why some would prefer either syntax for readability/style reasons. But the idea that one uses indices and the other not, seems highly dubious. Looking at the postgres manual [1], t…

I prefer the JOIN syntax because I don't have to rewrite everything when I realize later on that I need an OUTER JOIN.

Re: Best practices for writing SQL queries

#125

Post by majewsky » Sat, Apr 10, 2021, 9:56 PM UTC

sodapopcan wrote: post #68

fiddlerwoaroof wrote: post #52

Earlier quoted context omitted.

Nearly 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.

Yep—`-w` is the default when I blame in my editor but ya, github is really the problem.

At 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.

Re: Best practices for writing SQL queries

#126

Post by colonwqbang » Sat, Apr 10, 2021, 10:04 PM UTC

mtone wrote: post #118

colonwqbang wrote: post #101

> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’s index. This implies that WHERE style join can't use indices. I can understand why some would prefer either syntax for readability/style reasons. But the idea that one uses indices and the other not, seems highly dubious. Looking at the postgres manual [1], t…

> This syntax is not as commonly used as the one above This is going to need some sources. Is it true today? And why did they put parentheses in the ON condition? Worth nothing that there were variants of the WHERE syntax to support left joins using vendor-specific operators such as A += B, A = B (+) -- those are clearly deprecated today. [1] [2] I have a really hard time finding any source on the internet that recom…

I'm just quoting the manual here. I have no idea which style is really prevalent in the wild, now or X years ago.

My goal was only to cast doubt on the idea that WHERE clauses in general can't use indices.

Sure, let's debate what the nicest style is. But let's not claim that our preferred style somehow makes the DB go faster (without some kind of proof).

Re: Best practices for writing SQL queries

#127

Post by scottmcdot » Sat, Apr 10, 2021, 10:28 PM UTC

croes wrote: post #105

saila wrote: post #87

Earlier quoted context omitted.

This 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 It's…

It's also easier if you want to comment out certain parts of your code during debugging.

On this, I like to also use

   Where 1 = 1
   And...
   And...

Which makes it easy to comment out specific filters.

Re: Best practices for writing SQL queries

#128

Post by mtone » Sat, Apr 10, 2021, 10:30 PM UTC

colonwqbang wrote: post #126

mtone wrote: post #118

Earlier quoted context omitted.

> This syntax is not as commonly used as the one above This is going to need some sources. Is it true today? And why did they put parentheses in the ON condition? Worth nothing that there were variants of the WHERE syntax to support left joins using vendor-specific operators such as A += B, A = B (+) -- those are clearly deprecated today. [1] [2] I have a really hard time finding any source on the internet that recom…

I'm just quoting the manual here. I have no idea which style is really prevalent in the wild, now or X years ago. My goal was only to cast doubt on the idea that WHERE clauses in general can't use indices. Sure, let's debate what the nicest style is. But let's not claim that our preferred style somehow makes the DB go faster (without some kind of proof).

Indeed, they will perform the same or very close (the query plan might differ a bit due to the different orderings). Not sure where the author got that from. I'm complaining about the docs only.

Re: Best practices for writing SQL queries

#129

Post by Justsignedup » Sat, Apr 10, 2021, 10:59 PM UTC

some of these are inaccurate.

"a = 'foo'" is exactly the same performance as "a like 'foo'" and very close to the performance as "a like 'foo%'" and is fully indexed. When you put a wildcard in the front, the entire index is avoided, so you gotta switch to full text search.

Re: Best practices for writing SQL queries

#130

Post by ianmcgowan » Sat, Apr 10, 2021, 11:07 PM UTC

hn_throwaway_99 wrote: post #8

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…

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.