undefined | Better HN

0 pointsiooi7y ago0 comments

If I understand correctly from this post on dba [1], HAVING without GROUP BY would have the same effect as WHERE. Seems like they were just being pedantic for no good reason.

[1] https://dba.stackexchange.com/questions/57445/use-of-having-...

0 comments

oconnor6637y ago

If one of the columns you're SELECTing has some sort of complicated expression in it (e.g. `UNIX_TIME(colA) - UNIX_TIME(colB) AS duration`), I think you can refer to that column by name in HAVING, but not in WHERE. It's a pretty niche thing though; I can't remember if I've ever actually used it.

MBCook7y ago

I believe you’re correct.

However because HAVING takes place after all the results are fetched and processed (so it can do GROUP BYs) HAVING can’t use indexes.

So while the two will give you identical results if you’re not using a GROUP BY, the HAVING version could be thousands of times slower. Or worse.

samstave7y ago

How would this differ from contains?

j / k navigate · click thread line to collapse