Hacker News Reader

Comment by magicalhippo | Hacker News Reader

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.

View on HN

That’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.

Everywhere you can have a table expression, you can replace it with a subquery.

Indeed 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!

I’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.

Yes, it was a nice article. I just thought the title could be better, sounded a bit like click-bait.

You can also have a subquery in from as in

    select * from (select 1) as temp;

I’m sure Julie Evans knows a little bit about how databases work...

I wasn't knowing this before. Thanks for sharing!

Reply on news.ycombinator.com