
HAVING a Blunderful Time (or Wish You Were WHERE) (2007) - shangxiao
http://www.dcs.warwick.ac.uk/~hugh/TTM/HAVING-A-Blunderful-Time.html
======
cafard
The master pattern for this sort of thing is probably Babbage arguing with
Stephenson about whether a broader gauge would have been better for railroads.
Undoubtedly Babbage had the right of it in the sense that if one were starting
a railroad system from scratch the broader gauge would have been better. But
beyond some established base of rail mileage, it really didn't matter.

------
blattimwind
WHERE filters input rows

HAVING filters produced rows

Questions?

~~~
spthorn60
But he has a valid point in HAVING not being able to reference an aggregate
column by the AS name in the SELECT.

~~~
blattimwind
Yeah, I always considered that quite strange, given the order of the query
(and even though forward references are found almost everywhere in SQL, making
it even more strange)

ORM-wise it's not a huge issue, I tend to move them outside the query
definition to avoid the duplication. E.g.

    
    
        comment_count = func.count(Comment.id).label('comment_count')
        query = (session.query(User, comment_count)
                 .join(User.comments)
                 .group_by(User)
                 .having(comment_count > 10))

------
asah
Misleading title. Should be more like, "random whining about SQL"

SQL is like democracy: it's the worst form, except for every other.

Go try writing non-trivial queries in any other database query language, then
come back and whine to me about SQL.

Notably, SQL hides the implementation in a way that allows for 1000+x speedups
from index selection, JOIN order optimization, execution method and
parallelism. Typically, even hand-coded queries can't touch the performance
because of gonzo low level optimizations nobody would bother writing for one
query, such as optimizing batch size to match cache size.

Modern SQL (e.g. Postgres) even does this while allowing the user to include
unmodified business logic written in a variety of high level languages. For
example, if you want to search for employees that match some crazy predicate
written in (JavaScript/Python/etc) no problem, it fits right into the WHERE
clause, and is executed in parallel on each core of each node. You can even
index the results of this function, so the storage system doesn't even look at
non-matching records... assuming this index is the more selective of the
options, which it dynamically decides per query.

tl;dr: quit your whining

~~~
randomdata
_> Go try writing non-trivial queries in any other database query language_

Which query languages (other than ORMs) are the top contenders when staying
within the rational model to compare with?

While there are notable examples of non-relational databases that use SQL as
their native query language, I was recently looking for a relational database
that natively uses a language other than SQL and came up short. I imagine
there is something out there, but SQL dominates the landscape.

I am familiar with a few alternative languages, some of which compile to SQL.
But it is pretty bleak out there, which is interesting given how many general
purpose programming languages we have. I expect because ORMs have become the
language people use to query databases, all of the attention have gone into
them, rather than improving native query languages.

