11–20 of 84 posts
I very like the WITH statement in Postgres. It allow to break down the logic and composite the result. Any idea if it's standard SQL?
[strikeout, see comment above, it's in SQL99] MSSQL has a similar construct with Common Table Expressions (CTE's) though, so it' not unique to PG.
I very like the WITH statement in Postgres. It allow to break down the logic and composite the result. Any idea if it's standard SQL?
A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.
A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
WHERE 1=1
AND ...
--AND ...
AND ...
My personal style has a lot of similarities, but with some glaring differences. The biggest is that I put the comma in front of the next item, rather than trailing the one before. What this means is that when I add a new thing to the list of columns, I'm less likely to leave one out. See http://bentilly.blogspot.com/2011/02/sql-formatting-style.ht... for what this looks like in practice.
I'm not a big fan of this style. It's technically 'organized' (in the sense that there's a definite consistency), but it's vertical to the point of reduced readability: trivial queries can take up dozens of lines.
My general rule-of-thumb is that trivial things can have syntax constraints relaxed, as long as it makes them more readable, not less. I may adopt the style of the article for a complex query, but for something simpler I may go with:
SELECT foo, bar, baz
FROM my_table
WHERE foo=1 AND bar > 10
GROUP BY bar
ORDER BY baz
That said, if your SQL is intermixed with code whose sole purpose isn't to deal with that SQL, you are probably in for hurt later on anyway. If it is safely quarantined into data access routines of some sort, the size doesn't really matter, since the function or method containing the query should really be about that query.
A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
No, no, no. Comma first variable listing is the worst thing ever in the history of the world. Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.
Other than that, I use a variant of this style, where I put things on one line if possible (especially GROUP BY and ORDER BY). And I tend to line up my joins like so:
FROM foo
JOIN bar ON b.foo_id = f.id
LEFT JOIN baz ON ...Its a huge help when analyzing bad queries that newrelic might spit out.