131–140 of 158 posts
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.
(Edit: I think I misread “know” as “know of”; whoops.)
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.
My job involves a lot of ELT pipelines though and the queries I'm writing are often to transform a client's data from whatever ill-conceived data model they've been using to a standard data model that we use for all clients. Those queries require a lot more "detective work" to get right than the queries that run against our standard data model. If I was just writing queries against the standard, I'm not sure I'd spend enough time developing/debugging to really notice any ergonomic benefit.
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…
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
...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…
Sorry to rain on your parade, but there is nothing in that article that is not included in the basic SQL manuals like Itzik Ben-Gan's. 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…
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…
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…
Oddly, DataGrip doesn't provide this option.
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…
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
AND a.id = g.alpha_id
WHERE
a.val > 1
AND b.col 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…
Postgres and the likes allows trigram indexes for like queries, and expression based indexes just fine. This argument doesn't really pass the smell test.
Good blog post about it : https://about.gitlab.com/blog/2016/03/18/fast-search-using-p...
The docs: https://www.postgresql.org/docs/current/pgtrgm.html