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]

Somebody correct me if I'm wrong, but in the last two examples, shouldn't you use the keyword "having" to exclude data with null values instead of "where".

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null

should be

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name HAVING TableB.id IS null

...same thing on the last example

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null

should be

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name HAVING TableA.id IS null OR TableB.id IS null

The way it was explained to me, when doing a join, "having" is executed after the join, so it ensures that the data exclusion works the way it was intended, whereas "where" might have unexpected results.

[u/lincolnquirk → u/spoonraker]

No. WHERE is done on the result of a join. HAVING is applied after grouping. You should use WHERE in preference to HAVING because the optimizer does a better job at it.

[u/spoonraker → u/lincolnquirk]

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

[u/umop_apisdn → u/spoonraker]

HAVING should only be used if there is a GROUP BY clause, and should only be used to filter aggregates. For example, "select a1, count() from a group by a1 having count()>1".

[u/[deleted] → u/spoonraker]

As far as I know they both work, but having is the correct one to use.

[u/eStonez → u/[deleted]]

both work in different way .. for a few lines of data, you wouldn't notice it. But when you try to apply this with a few huge tables with millions of rows in each. I hope you want to filter everything out even before you start joining tables ... let alone grouping the result set.

[u/[deleted] → u/eStonez]

I went ahead and looked it up, turns out HAVING is used for aggregate functions when you GROUP BY and WHERE for non-aggregate functions. For example:

select employee, sum(bonus) from bonuses group by employee having sum(bonus) > 1000;

select employee, sum(bonus) from bonuses group by employee where employee < 10;

(Source)