Re:Our SQL interview questions
by socialist_coder on Tuesday May 28, 2013 @01:49PM
(#5781429)
That's messed up. "Having" should only be used for filters on the output aggregate functions, and "where" should be used for filters on the input row data. If mysql lets you use "having" when you mean "where", that is unfortunate.
example:
select count(1) cnt, department
from sales
where department_id in (1, 2, 3, 4, 5)
group by department
having count(1) >= 100;
So, it filters out all the input rows to only those department ids, and then it filters out the aggregate output rows to only those with a count() of 100 or more.This is how Oracle and MS-SQL server work.
About 99% of the noise about mysql vs pgsql boils down to this overarching philosophical different of "try yer best" vs "only perfection is permissible". There are minor other differences aside from that, none of which I can remember at this time.
I was mostly trying to make a joke and making the psuedocode kinda sql inspired rather than cut and paste into a window like a stack exchange answer. I could have implemented it "properly" as a nested subquery I suppose. Or to make the point a little more .. obviously, just "select 0;"