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

141–150 of 158 posts

Re: Best practices for writing SQL queries

#141

Post by mobjack »

ianmcgowan wrote: post #134
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…

I do this style too, but think of each new row as basically a new table being added to the query (especially if it's a query, which may go over several rows and is indented): from alpha a inner join beta b on b.id = a.id left outer join gamma g on g.id = a.id left outer join ( select z.id, count(\*) as cnt from zeta z ) delta on delta.id = a.id where ...

That is my preferred style.

If there are many joins, I could also add more indention to show what is being joined to what.

Re: Best practices for writing SQL queries

#142

Post by mulmen »

vericiab wrote: post #133
mulmen wrote: post #93

Earlier quoted context omitted.

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.

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…

I hear this argument all the time but it makes no sense. Leading commas only help you comment out the last line. Any comma arrangement allows you to comment any intermediate line. It’s sacrificing readability and aesthetics for a tiny benefit on one row.

Re: Best practices for writing SQL queries

#143

Post by [deleted] »

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…

[deleted]

Re: Best practices for writing SQL queries

#144

Post by gjvc »

zephharben wrote: post #138
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…

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

Would it be rude of me to ask you to open an item on https://youtrack.jetbrains.com/ for this please? I'm just a curious JetBrains DataGrip user.

Re: Best practices for writing SQL queries

#145

Post by jaredsohn »

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…

An argument for using the where syntax in simplish adhoc queries: It is a lot shorter and has simpler syntax. Speed is more important than maintainability in this context and it shouldn't result in errors.

Basically, instead of writing

"inner join table2 on"

you can just write "and" and put it after your other where clauses.

It doesn't result in errors because the query will fail if referring to fields from the second table when there is no join.

Could even put the join where clauses on a separate line to split them out from the other where clauses.

It does require reworking the query if not doing an inner join but that is what at least I usually want when doing ad hoc queries. Agree that the join syntax should be used in production code.

Re: Best practices for writing SQL queries

#146

Post by nickpeterson »

barrkel wrote: post #91
johnvaluk wrote: post #5

Overall an enjoyable read, but as someone who includes SQL queries in code, I disagree with two points: I despise table aliases and usually remove them from queries. To me, they add a level of abstraction that obscures the purpose of the query. They're usually meaningless strings generated automatically by the tools used by data analysts who rarely inspect the underlying SQL for readability. I fully agree that you sh…

Table aliases make queries much less verbose than using the full table name, and more readable as a result. Aliases are unavoidable when you're joining the table more than once. Not using qualified identifiers is just asking for trouble. Short aliases - I tend to use the first letter of each word in the table name - work best, IMO.

I tend to use two character because you might join similar named tables or the same table multiple times.

Re: Best practices for writing SQL queries

#147

Post by Semaphor »

twoodfin wrote: post #137
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…

Prefer EXISTS to IN is also a bit odd, as the latter is trivially transformed to the former. The DBMS I work with does it universally.

I’ve had them get treated equally and differently both in SQL Server.

Re: Best practices for writing SQL queries

#148

Post by supernova87a »

wolf550e wrote: post #58
supernova87a wrote: post #53

Earlier quoted context omitted.

>Join on floating point value is quite rare. Why do you need to do that? Ah, thanks for noticing this. They are, for example, (1) tables of timestamped events, and (2) tables of time ranges in which those events need to be associated with (but which unfortunately were not created with that in mind at the time)... So for example FROM tableA LEFT JOIN tableB ON (timestampA BETWEEN timestampB1 AND timestampB2) (and wher…

Since it's a left join, you will get all the rows from tableA, and for each row the matching rows in tableB. If the ranges in tableB are non-overlapping, maybe you have names for time ranges and you want the name of the time range for each row in tableA? If tableB is large, I don't know what any particular query planner will do with such a query and whether an index on (timestampB1, timestampB2) will help. It should,…

Thanks for that!

Re: Best practices for writing SQL queries

#149

Post by Tomis02 »

willvarfar wrote: post #43
Hjfrf wrote: post #7

Lots of mistakes (or at least rare opinions going against the crowd) here. Here's a better general performance tuning handbook - https://use-the-index-luke.com/

More that its a dumbed-down general guide aimed at meta base users? Use-the-index-luke is an altogether deeper, more technical article aimed at data engineers and going into the details and differences between databases.

> aimed at data engineers

I disagree. Professional developers should know their database of choice inside and out, and use-the-index-luke helps with that. You can skip the details about databases that aren't relevant to you.

Re: Best practices for writing SQL queries

#150

Post by gopalv »

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…

> the WHERE syntax is clearly presented as the main way of inner joining tables.

As someone who debugs a lot of SQL, I prefer the ON clause for one major reason - it is easy to notice it if it is missing.

So there was a customer who had a wrote 300 terabytes of intermediate data out of a badly written query, which wasn't caught because they ran the equivalent of

"select * from orders, customers ... "

and ended up commenting out the o_cust_id = c_cust_id in the bottom of the where clause while they were messing about with it.

And another example of a CTE which was missing the ON clause, but the outer query did have a where clause and that was great till someone else cut-pasted the CTE into a materialized view & tried to rebuild it.

> Maybe some database somewhere cannot optimise queries properly unless JOIN is used?

Until Apache calcite came in, Apache Hive could not always find the table which was joined against out of a where clause (so you'd find TPC-H queries which were planned as cross-products between the same table with different aliases etc - like Query 7 was badly planned from the beginning of Hive till Hive 1.2 and only optimally planned in Hive 3.x).

But SQL engines have gotten much better over the years & most people should write it for readability than for execution order, but the readability is really why I like the ON syntax, particularly while chop-debugging a big query written by some poor guy who was translating COBOL into SQL.

Post reply on HN