News for hackers. Stuff that matters.
Re:SQL as an API language by davidgould on Thursday October 04, 2018 @08:51PM (#18144853)
HAVING is not the same thing as WHERE. WHERE filters the query to produce a result set. HAVING filters the result set.

    select id, count(*) - 1 as nduplicates
       from parts where color = 'red'
       group by id
       having count(*) > 1;
WHERE cannot filter on the count(*) because WHERE is applied during the query before the count is known.

I agree SQL is kind of warty, and it would be great if it were easier to compose from programs, but HAVING is not redundant to WHERE.

Re:SQL as an API language by irishsultan on Friday October 05, 2018 @07:31AM (#18147261)
But that's equivalent to the following query which doesn't use having, only select

    select * from (select id, count(*) - 1 as nduplicates
       from parts where color = 'red'
       group by id) _inner
       where nduplicates > 0;

Now obviously that doesn't read as easily, but WHERE can filter out the count(*) perfectly.
Re:SQL as an API language by aidos on Friday October 05, 2018 @04:04AM (#18146500)
I believe this should have been in response to its sibling. The set theorist in me agrees - the two operations are working on 2 different sets at 2 different levels of abstraction.
Re:SQL as an API language by davidgould on Sunday October 07, 2018 @01:00AM (#18158917)
Yes, replied to the off by one post.