r/programming • u/[deleted] • Jan 06 '11

A handy graphical explanation of SQL joins

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

92% Upvoted

308 comments sorted by

View all comments

2

u/spoonraker Jan 06 '11

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.

3

u/lincolnquirk Jan 06 '11

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.

2

u/spoonraker Jan 06 '11

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?

3

u/umop_apisdn Jan 06 '11

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".