Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Having, a less understood SQL clause

smallthingssql.com

Post reply on HN
Print view View original

1–10 of 83 posts

Re: Having, a less understood SQL clause

#3

Post by teddyh »

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equivalent to SELECT * FROM … WHERE … GROUP BY … HAVING …;)

Re: Having, a less understood SQL clause

#4

Post by syntheticcdo »

teddyh wrote: post #3

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equiva…

FWIW I just tested a somewhat complex query using HAVING vs a sub-select as you indicated and Postgres generated the same query plan (and naturally, results) for both.

Re: Having, a less understood SQL clause

#5

Post by galkk »

Having 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)

Re: Having, a less understood SQL clause

#7

Post by djbusby »

syntheticcdo wrote: post #4
teddyh wrote: post #3

HAVING is less understood? There’s nothing strange about HAVING, it’s just like WHERE, but it applies after GROUP BY has grouped the rows, and can use the grouped row values. (Obviously, this is only useful if you actually have a GROUP BY clause.) If HAVING did not exist, you could just as well do the same thing using a subselect (i.e. doing SELECT * FROM (SELECT * FROM … WHERE … GROUP BY …) WHERE …; is, IIUC, equiva…

FWIW I just tested a somewhat complex query using HAVING vs a sub-select as you indicated and Postgres generated the same query plan (and naturally, results) for both.

A DB wizard I used to work for showed me HAVING after looking at this nasty sub-sub select I did, with some app-layer-loop after.

Caesar if you see this thanks for being a great mentor.

Re: Having, a less understood SQL clause

#9

Post by mastax »

I like using HAVING just to have conditions that reference expressions from the SELECT columns.

e.g. rather than having to do

    SELECT
        COALESCE(extract_district(rm.district), extract_district(p.project_name), NULLIF(rm.district, '')) AS district,
        ...
    FROM ...
    WHERE COALESCE(extract_district(rm.district), extract_district(p.project_name), NULLIF(rm.district, '')) IS NOT NULL
just do

    SELECT
        COALESCE(extract_district(rm.district), extract_district(p.project_name), NULLIF(rm.district, '')) AS district,
        ...
    FROM ...
    HAVING district IS NOT NULL
Hopefully the optimizer understands that these are equivalent, I haven't checked.
Post reply on HN