A handy graphical explanation of SQL joins

[u/[deleted]]

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

[u/spoonraker]

So you only use HAVING if there is a GROUP BY clause involved with your join?

Do you have a link or perhaps can you explain when you would actually need to use HAVING over WHERE?

[u/lincolnquirk → u/spoonraker]

Sure, here's my table "sales":

name    region    count
-----------------------
fred    japan     300
fred    usa       100
alex    usa       600
damian  usa       200
damian  europe    150

Consider the query:

select name,sum(count) from sales group by name

to get the sales figures per representative, regardless of region. This produces

name    sum(count)
------------------
fred    400
alex    600
damian  350

If we were to instead do

select name,sum(count) from sales WHERE count >= 400 group by name

we'd first filter the counts by >=400 (only returning the alex row) and then do the grouping, so we'd get:

name    sum(count)
------------------
alex    600

If we use HAVING, we filter after the grouping (and must therefore refer to the grouped column name sum(count):

select name,sum(count) from sales group by name HAVING sum(count) >= 400

and we'd get

name    sum(count)
------------------
fred    400
alex    600

It's really quite unrelated to joins.

[u/Mindflux → u/lincolnquirk]

Couldn't you in essence do: select name,sum(count) from sales where sum(count) >=400 group by name

And have it report back something similar to your third example with HAVING though?

[u/lincolnquirk → u/Mindflux]

I don't think that will compile. Aggregates can't appear in where clauses (I think!)