My SQL knowledge is very limited - I had heard of HAVING but not GROUP BY CUBE or COALESCE - but one thing stood out: "The rewritten sql ... ran in a few seconds compared to over half an hour for the original query."
I know there were four million rows in the dataset, but is 30 minutes the kind of run-time you would expect for a query like this?
Having, a less understood SQL clause
31–40 of 83 posts
Re: Having, a less understood SQL clause
#32My SQL knowledge is very limited - I had heard of HAVING but not GROUP BY CUBE or COALESCE - but one thing stood out: "The rewritten sql ... ran in a few seconds compared to over half an hour for the original query." I know there were four million rows in the dataset, but is 30 minutes the kind of run-time you would expect for a query like this?
> I know there were four million rows in the dataset, but is 30 minutes the kind of run-time you would expect for a query like this?.
Clearly not since they got it down to a few seconds ;).
But tongue in cheek aside, it's incredibly dependent on what is in those 4M rows, how big they are, if they are indexed, whether you join in weird ways, whether the query planner does something unexpected.
SQL tooling is by and large pretty barbaric compared to most other tool these days, Intellij (and various spin off language specific IDE's) have the best tooling I've seen in that area but even then it's primitive.
Re: Having, a less understood SQL clause
#33Having vs where is my first question to filter candidates who have less experience in sql than they claim.
Well, I used SQL professionally for at least 7 years in different capacities and “having” was a new construct to me. You might say that I suck at SQL or you might realize that there’s more than one way to achieve a result in SQL. I usually prefer “with” statements. I hope this is not the only criteria you use to filter candidates.
In my mind a better question would be to state the problem and see if the candidate can come up with a working SQL statement.
Re: Having, a less understood SQL clause
#34Well, I consider myself somewhat fluent with SQL, and for some reason left joins are the ones that occasionally get me really confused - so much so that I actively try to avoid them. Trouble is not in the vanilla cases, but when you start throwing multiple tables and multiple where clauses in the same query, then there is something about left joins and nulls that is really unintuitive to my brain. Maybe I should spen…
I think left join is a bad name. Probably something like OPTIONAL JOIN or TRY JOIN would be more obvious. Of course the problem is then what do you call a right join? REVERSE OPTIONAL JOIN? But that is getting pretty confusing now. But maybe worth it because in my experience left is far more common.
Re: Having, a less understood SQL clause
#35Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)
I've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly:
`group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately for every function.
Re: Having, a less understood SQL clause
#36HAVING is a hack. Because SQL has such an inflexible pipeline we needed a second WHERE clause for after GROUP BY.
It would be nice if we could have WHERE statements anywhere. I would like to put WHERE before joins for example. The optimizer can figure out that the filter should happen before the join but it is nice to have that clear when looking at the code and it often makes it easier to read the query because you have to hold less state in your head. You can quickly be sure that this query only deals with "users WHERE deleted" before worrying about what data gets joined in.
Re: Having, a less understood SQL clause
#37Calling each select "a sql" is really cute
It doesn't irk me anything like as much as 'a Docker' (a Docker what? Usually container) for some reason.
Although perhaps that shouldn't annoy me anyway, it ought to be better than being specific but inaccurate (which you could probably expect from someone unfamiliar enough to say 'a Docker') - mixing up image/container as people do.
Re: Having, a less understood SQL clause
#38HAVING is a hack. Because SQL has such an inflexible pipeline we needed a second WHERE clause for after GROUP BY. It would be nice if we could have WHERE statements anywhere. I would like to put WHERE before joins for example. The optimizer can figure out that the filter should happen before the join but it is nice to have that clear when looking at the code and it often makes it easier to read the query because you…
You can put WHERE before joins using nested queries, i.e. "FROM (SELECT * FROM users WHERE deleted") INNER JOIN ...".
Re: Having, a less understood SQL clause
#39Forget HAVING -- in my opinion RANK is the most useful SQL function that barely anyone knows about. Has gotten me out of more sticky query issues than I can count.
Re: Having, a less understood SQL clause
#40How would one use the query result in the example? Every rows having multiple grouping levels seems like a hard result set to use in any capacity other than a human reading it and interpreting it directly