Subject | Re: [firebird-support] filter conditions: WHERE vs. JOIN clause |
---|---|
Author | W O |
Post date | 2014-05-29T18:51:41Z |
Thank you very much Set for your answer.
Greetings.
Walter.
On Thu, May 29, 2014 at 6:28 AM, Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
I think this is very simple, Walter, Ann is just saying that>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)."
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