Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Best practices for writing SQL queries

metabase.com

Post reply on HN
Print view View original

101–110 of 158 posts

Re: Best practices for writing SQL queries

#101

Post by colonwqbang »

> 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], the WHERE syntax is clearly presented as the main way of inner joining tables. The JOIN syntax is described as an "alternative syntax":

> This [INNER JOIN] syntax is not as commonly used as the one above, but we show it here to help you understand the following topics.

Maybe some database somewhere cannot optimise queries properly unless JOIN is used? Or is this just FUD?

[1] https://www.postgresql.org/docs/13/tutorial-join.html

Re: Best practices for writing SQL queries

#102

Post by SigmundA »

tomnipotent wrote: post #94
SigmundA wrote: post #89

Earlier quoted context omitted.

Yes every row of the index needs to be scanned not every row of the table which is faster than scanning the table. I am most familiar with MS SQL server and it will most certainly do an index scan for what it thinks is a highly selective predicate with "suffix wildcards" and it can return results faster than scanning the table. If the index covers the result columns it will scan the index and never touch the table ot…

B-tree's do not work that way. They are inherently ordered, and contain min/max that help to determine if you can skip the page for a given condition. The min/max cannot be used for suffix wildcards. Unless the index contains all the columns you're dealing with, the optimizer will determine that just scanning the table will cost less than scanning an index AND then looking up the data in the table (bookmark lookups i…

B-trees have little to do with it, if the table has many columns its cheaper to scan the index for the value because it occupies less pages, thats all, less I/O more cache hits etc, goes from top to bottom on the index scanning for the result. This is the distinction between scan and seek.

I just ran a common one I see and yep MS SQL is still doing a index scan then key lookup to get result with a select * from table where col LIKE '%abc' type query.

Re: Best practices for writing SQL queries

#103

Post by u801e »

saila wrote: post #87
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 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…

I'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.

Re: Best practices for writing SQL queries

#104

Post by dbatten »

saila wrote: post #87
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 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…

My company style guide explicitly specifies this.

https://github.com/republicwireless-open/sql-style-guide

I think Mozilla's does as well.

Re: Best practices for writing SQL queries

#105

Post by croes »

saila wrote: post #87
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 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.

Re: Best practices for writing SQL queries

#106

Post by matt-snider »

adrianmonk wrote: post #76
tremon wrote: post #44

Avoid functions in WHERE clauses Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, p…

Also, some databases allow you to index the result of a function. Oracle calls them "function-based indexes". PostgreSQL seems to call them "indexes on expressions". And MySQL seems to support "generated columns" which can be "virtual" and can have indexes. (Although in that case the expression lives in the column definition, so it's not actually in a where clause.) Also, I guess some databases probably let you have…

MySQL 8.0 actually added support for functional indexes, but I found out the hard way that they don't work the same way as indexing generated columns. For me the biggest issue were the caveats around using the indexed column in conditions, which resulted in the index not being used in surprising situations. Anyways, I had to revert to generated columns which was a shame because it was a feature I had looked forward to using.

I found this article on the topic to be helpful:

https://saveriomiroddi.github.io/An-introduction-to-function...

Re: Best practices for writing SQL queries

#107

Post by SigmundA »

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…

Yes the optimizer should end up with the same plan either way, although the ON syntax is SQL standard.

Re: Best practices for writing SQL queries

#108

Post by hobs »

mulmen wrote: post #86
hobs wrote: post #23

Earlier quoted context omitted.

Most of your queries shouldn't be so verbose as to confuse which aliases you are talking about, but I agree - here's the best format :) (because why put ON on another line anyway?) SELECT bo.title, au.last_name, au.first_name FROM books AS bo LEFT JOIN authors AS au ON bo.author_id = au.id

ON should be on another line because it gives context to bo.author_id = au.id.

You never write NOT ON, so no, I'd disagree that its required for all the inequalities you express.

Re: Best practices for writing SQL queries

#109

Post by joelcollinsdc »

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…

Maybe not for indexes but what about using a sql syntax that is more common and extensible?

Re: Best practices for writing SQL queries

#110

Post by ZeroClickOk »

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…

Reference: https://www.sqlstyle.guide/
Post reply on HN