111–120 of 158 posts
> 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.
Earlier quoted context omitted.
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.
> 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…
Also a few things are dead wrong: the "make the haystack small" is optimization (it should be at the end, as the first rule says), the "prefer UNION ALL to UNION" is missing the context (a good dev knows what is needed, not what to prefer) and the usage of CTEs is nice, but sometimes slower that other options and in SQL slower can easily be orders of magnitude, so nice is not enough. Same for 'avoid sorting where possible, especially in subqueries' or "use composite indexes" (really? it's a basic thing, not a best practice).
In the past few months I interviewed and hired several DBAs, this list is ok-ish for a junior but a fail for a senior. I am not working for FAANG, so the bar is pretty low, this article would not even pass for a junior there.
Earlier quoted context omitted.
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.
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.
You can easily add remove columns like this, just removing the entire line. If you add the comma the traditional way, you will change 2 lines (the end of previous line with comma, and the new line). It's nice for maintenance and diff'ing
col1,
col2,
col3
col1
,col2
,col3
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…
I find it more readable as 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 Usually only the conditions get deep and can also use extra indented parenthesized parts. reminder: don't use 'OUTER' it's pure noise
> 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 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 recommends using the WHERE style joins. So by extension, I wouldn't expect to be used much anymore except for legacy projects. MS SQL Server docs docs mention ON syntax being "preferred" [3], and MySQL says "Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set." [4]
The PostgreSQL docs seem misleading and outdated to me.
[1] https://docs.microsoft.com/en-us/archive/blogs/wardpond/depr...
[2] https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri...
[3] https://docs.microsoft.com/en-us/sql/relational-databases/pe...
Earlier quoted context omitted.
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.
ON is required by the syntax. Why start every line with a keyword that describes the line except the ON portion of a JOIN? It's inconsistent and has no clear benefit.
> 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 don’t think I have ever seen that way of doing an inner join in the wild, despite working as a DBA or data engineer for the past 15 years, 10 of those Postgres-only roles.