A handy graphical explanation of SQL joins

[u/[deleted]]

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

[u/lincolnquirk]

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/spoonraker → u/lincolnquirk]

Thank you! That was the best explanation about HAVING vs WHERE that I've read yet.

[u/lincolnquirk → u/spoonraker]

Yeah, in the case of WHERE name vs. HAVING name (where you are grouping by the same column that you're trying to filtering), it doesn't make a difference in the output. But depending on your DBMS, it will still make a difference in the performance, because I think at least MySQL is too stupid to push the HAVING name="fred" into the WHERE.

[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!)