On Thu, May 29, 2014 at 6:28 AM, Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

>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