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

91–100 of 158 posts

Re: Best practices for writing SQL queries

#91

Post by barrkel »

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.

Re: Best practices for writing SQL queries

#92

Post by Icathian »

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…

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

Re: Best practices for writing SQL queries

#93

Post by mulmen »

arh68 wrote: post #73
dspillett wrote: post #24

Earlier quoted context omitted.

I'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 or SELECT a.foo , b.bar , g.zed FROM alph…

I like your second version. My own style, still evolving, is to write more lines and align further left select a.foo , b.bar , g.zed from alpha a inner join beta b on b.alpha_id = a.id and b.thing = a.another left join gamma g on g.beta_id = b.id where a.val > 1 and b.col

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.

Re: Best practices for writing SQL queries

#94

Post by tomnipotent »

SigmundA wrote: post #89
tomnipotent wrote: post #83

Earlier quoted context omitted.

> Depends on estimated selectivity This can't be determined with LIKE suffix wildcards and that's not how any of the commonly-used index data structures work (b-tree, hash, gist, or bitmap). Index metadata will not help in eliminating leaf pages, and every row is going to need to be scanned.

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 in MSSQL).

Re: Best practices for writing SQL queries

#95

Post by mulmen »

hn_throwaway_99 wrote: post #26
Hjfrf wrote: post #21

Earlier quoted context omitted.

Does that still look ok if you're selecting 10+ columns with functions, or would you split out the first line situationally?

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

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

Re: Best practices for writing SQL queries

#96

Post by mulmen »

jolmg wrote: post #80
hn_throwaway_99 wrote: post #26

Earlier quoted context omitted.

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

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

With the curly braces this makes a lot more sense. In SQL it offends my eyes (personal preference) but here it seems more clear.

Re: Best practices for writing SQL queries

#97

Post by zikzak »

akira2501 wrote: post #57
thih9 wrote: post #36

Earlier quoted context omitted.

Maintaining 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 (bonus: "AND" got accidentally aligned with the end of "WHERE")

> Maintaining alignment in these queries seems a pain. I use tabs. SELECT t.foo, t.bar FROM a_table t

This escalated quickly.

Re: Best practices for writing SQL queries

#98

Post by mulmen »

grzm wrote: post #69
bob1029 wrote: post #65

This seems like reasonable discussion, but you would get far more traction if you have an opportunity to write an entire schema from scratch in the proper way. Not having to fight assumptions along lines of improperly denormalized columns (i.e. which table is source of truth for a specific fact) can auto-magically simplify a lot of really horrible joins and other SQL hack-arounds that otherwise wouldn't be necessary.…

One of the strengths of Metabase is that it can plug into a variety of data sources, not just RDBMS. For example, AWS Athena over data in S3 buckets. Good design can still make things easier, of course, but not always an option. Relational purity is not going to be an option in such circumstances, so are in my opinion correctly not addressed in the piece.

Metabase sounds like a great tool for building clean analytic schemas then. You still need to design those schemas though.

Re: Best practices for writing SQL queries

#99

Post by mulmen »

alex_anglin wrote: post #34
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…

To each their own, but in the case of ETL/ELT, you would just be asking for pain not using aliases.

In the case of ETL you should only be referencing those tables a few times because you are integrating them into friendly analytic models. In that case you probably have a lot of columns to wrangle and complex transformation logic. In those cases I prefer to use no alias at all to avoid the scrolling around to get context, even when table names are very long.

Re: Best practices for writing SQL queries

#100

Post by mulmen »

magicalhippo wrote: post #64
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…

I got descriptive table names like WhsTransactionGoodsItems and WhsTransactionGoodsItemPackages. I feel it would be rather noisy to have to specify such table names in front of the 15+ column references in a query, compared to using aliases. Then again I've never had to diff the result sets, so I guess our usage is quite different.

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.
Post reply on HN