>Hello Ann
>
>Can you please explain better that phrase?
>
>"With outer joins, a condition in the WHERE clause that affects the right side
>of a left outer join (the one that doesn't have to exist) effectively
>turns off the outerness of the join (unless it includes OR <value> IS NULL)."
I think this is very simple, Walter, Ann is just saying that
a)
SELECT P.Name, C.Mayor
FROM PERSONS P
LEFT JOIN CITIES C ON P.CITY = C.CITY
WHERE P.FIRST_NAME = 'Walter'
AND C.COUNTRY = 'Laos'
would only return the persons residing in Laos (outerness turned off), whereas
b)
SELECT P.Name, C.Mayor
FROM PERSONS P
LEFT JOIN CITIES C ON P.CITY = C.CITY
AND C.COUNTRY = 'Laos'
WHERE P.NAME = 'Walter'
will return all persons named Walter regardless of where they live (the mayor
of the town being included if the person lives in Laos).
SELECT P.Name, C.Mayor
FROM PERSONS P
LEFT JOIN CITIES C ON P.CITY = C.CITY
WHERE P.FIRST_NAME = 'Walter'
AND (C.COUNTRY = 'Laos' or C.COUNTRY IS NULL)
is an example of the exception that Ann notes ("unless it includes OR <value>
IS NULL")
Admittedly, the above example makes no sense, but has its uses in other cases.
The way I most commonly use IS NULL with LEFT JOIN will be a variation of b):
SELECT P.Name
FROM PERSONS P
LEFT JOIN CITIES C ON P.CITY = C.CITY
AND C.COUNTRY = 'Laos'
WHERE P.NAME = 'Walter'
AND C.COUNTRY IS NULL
This basically gives you the name of all Walters excepting those that live in
Laos (it is an alternative to using NOT EXISTS).
HTH,
Set
