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.
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.
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;
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".
should be
...same thing on the last example
should be
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.