3 ms·
Title is misleading as far as I can tell. The author was just surprised to realize that the syntax for SQL queries did not mirror the execution order of the var
by magicalhippo 7y ago
Title is misleading as far as I can tell. The author was just surprised to realize that the syntax for SQL queries did not mirror the execution order of the various elements of the query.
It is a bit of a "well, duh" thing, but I've had my fair share of those with SQL through the years so I won't hold it against the author.
One of my more recent random discoveries was that I could join a subquery, ie
select x.a, y.b, y.c
from tableX x
join (
select id, sum(b) as b, count(b) as c
from tableY group by id
) as y on y.id = x.id
Again nothing special, just hadn't had the need for it before so didn't even think about the possibility.
- thescribbblr 7y agoI wasn't knowing this before. Thanks for sharing!
- DasIch 7y agoYou can also have a subquery in from as in select * from (select 1) as temp;
- goto11 7y agoEverywhere you can have a table expression, you can replace it with a subquery.
- Sharlin 7y agoIndeed one can think of a table expression as the trivial subquery SELECT * FROM table …well, modulo the fact that in a concrete sense that substitution would lead to infinite recursion!
- Sharlin 7y agoThat’s one of the insights that Everyone Should Have about RDBs and SQL. Relational databases are about, well, relations, in particular the sort of discrete relations that can be represented as a set of tuples. It doesn’t matter where those sets of tuples come from. From the disk? Great. Computed on the fly? That’s fine too. And by manipulating relations you get… new relations! What RDBs and SQL are actually about is composing relations in the way that, say, Haskell is about composing functions. And indeed the theory behind RDBs is called relational algebra.
- thawaway1837 7y agoI’m pretty sure the author was aware that the execution order was not the same. What the author was not aware of was what the actual execution order is. This article is about them actually digging in to find out what that order was.
- magicalhippo 7y agoYes, it was a nice article. I just thought the title could be better, sounded a bit like click-bait.
- scarface74 7y agoI’m sure Julie Evans knows a little bit about how databases work...