|
|
Posted by Erland Sommarskog on 10/01/02 11:19
thilbert (thilbert@gmail.com) writes:
> Thanks Debian, that did it. Is it then incorrect to use the WHERE
> clause when using an outer join? I've looked at books on-line and
> could not find a reference to using WHERE clauses with outer joins,
> however they appear to be a typical part of inner joins.
Incorrect and incorrect, it has a different meaning. The FROM clause
lasts all the way to WHERE, so you have
FROM a LEFT JOIN b ON a.col = b.col
Then the WHERE is applied. But if you say
WHERE b.othercol = 3
you are effectively filter out the rows from a which did not have a
matching row in b.
If you say
FROM a LEFT JOIN b ON a.col = b.col AND b.othercol = 3
The condition of othercol becomes part of the join, so that for
rows where a.col = b.col but b.othercol = 2 you will get NULL
values for b.*.
You can also say:
FROM a LEFT JOIN b ON a.col = b.col
WHERE b.othercol = 3 OR b.col IS NULL
But this gives a different result. Here the rows where a.col = b.col
but b.othercol = 2 will be removed from the result set.
See also Celko treatise on the subject.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|