Thank you very much Set for your answer. Greetings.
Walter. On Thu, May 29, 2014 at 6:28 AM, Svein Erling Tysvær [email protected] [firebird-support] < [email protected]> 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 > > >
