Re: Problems with an outer join.

Thank you for your answer, very explanatory, but of course I do not agree with the way an outer join is perform. Jonathan Leffler <jleffler@informix.com> on 10/14/98 01:39:09 PM To: Rafal Furdzik/PaulWeiss cc: informix-list@iiug.org Subject: Re: Problems with an outer join. Please use shorter subject lines -- you are not intended to write essays as the Subject. On Wed, 14 Oct 1998 rfurdzik@paulweiss.com wrote: > When you do an outer join with another table to which you applied criteria > which in result returns no data, an outer join still will be performed joining > a row from first table with an empty row. Please help. > > drop table tempa; > drop table tempc; > create table tempA(PK Integer, FK INTEGER, FK2 Char(1)); > create table tempC(PK Char(1)); > INSERT INTO tempA VALUES (10, null, 'A'); > INSERT INTO tempA VALUES (20, 10, 'B'); > INSERT INTO tempA VALUES (30, 40, 'A'); > INSERT INTO tempC VALUES ('A'); > INSERT INTO tempC VALUES ('B');> > select A.PK, A.FK, B.PK, B.FK2, C.PK > FROM tempA As A, OUTER (tempA As B, tempC As C) > Where A.FK=B.PK > AND B.FK2=C.PK > AND C.pk is not null > AND C.pk In('A')> > It is showing rows where C.pk is null an is not in 'A'. Is it a bug ???? No. It is designed to work like that. No, you might want to argue that the design is incorrect, but that's a different matter. You've asked for all the rows in A to be retained; therefore they are retained. Effectively, the outer join is done later than the filtering on C.pk, though it is very complicated to explain. You can find some discussion in my book "Using Informix-SQL", and you can find a critique of the implementation and/or my discussion of it in one of C J Date's Relational Writings books (either the 1991-94 or the previous (1988-91?) one). Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn