Best practices for writing SQL queries
metabase.com
Best practices for writing SQL queries
1–10 of 158 posts
Re: Best practices for writing SQL queries
#2Does anybody else like putting from first? I find it makes the auto complete sooo much better and easier to read.
Re: Best practices for writing SQL queries
#3>LIKE compares characters, and can be paired with wildcard operators like %, whereas the = >operator compares strings and numbers for exact matches. The = can take advantage of indexed columns.
Unless this specific to certain databases, LIKE can take advantage of indexes too, without wildcards LIKE should be nearly identical in performance to = both seeking the index.
>Using wildcards for searching can be expensive. Prefer adding wildcards to the end of strings. Prefixing a string with a wildcard can lead to a full table scan.
Which is contradictory to the first quote, it seems you recognize that a wildcard at the end can take advantage of an index. Full table scan is the same thing as not taking advantage of an index, hence LIKE can take advantage of normal indexes so long as there are characters before the first wildcard or has no wildcards.
Re: Best practices for writing SQL queries
#4> Avoid
SELECT
title,
last_name,
first_name
FROM books
LEFT JOIN authors
ON books.author_id = authors.id
SELECT
b.title,
a.last_name,
a.first_name
FROM books AS b
LEFT JOIN authors AS a
ON b.author_id = a.id
Re: Best practices for writing SQL queries
#5Overall 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 should reference columns explicitly with the table name, which I think is the real point they're trying to make in the article.
While it's true that sorting is expensive, the downstream benefits can be huge. The ability to easily diff sorted result sets helps with troubleshooting and can also save significant storage space whenever the results are archived.
Re: Best practices for writing SQL queries
#6I'd add "be aware of window functions"[1]. Certain gnarly aggregates and joins can often be much better expressed using window functions.
And at least for the database we use at work, if the sole reason for a join is to reduce the data, prefer EXISTS.
Re: Best practices for writing SQL queries
#7Lots 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/
Re: Best practices for writing SQL queries
#8This 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 Re: Best practices for writing SQL queries
#9> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.
I agree with the source that the latter (explicit table specification in the SELECT list, whether using aliases or not) is to be preferred to the former; at the same time (while I am sometimes guilty of using them) I agree that single-character aliases are generally a poor choice for the same reasons that’s generally true of single character identifier names; column aliases are variable (well, constant) names and the usual rules of meaningful identifier names apply.
Re: Best practices for writing SQL queries
#10> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.
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.