Re: Outer Join Bug (was It's Incredible)

this is not a bug at all! you are asking for all rows from A where colA is null. an outer join, as informix defines it, will return null if the where clause is not satisfied. so if the value of colA is null, null is returned otherwise null is returned. what else would you expect this query to return for colA? } } In article <3na60a$6ff@ixnews3.ix.netcom.com>, bmaclean@ix.netcom.com (Bill } MacLean) wrote: } } > [CLIPPED] } > I saw someone else (under the "Incredible" thread) say he had no } > problems with outer joins, and I saw another person say that his server } > crashed on certain outer joins. Crashing is bad, but incorrect result } > sets would be worse. } > } > Can anyone speak authoritatively on this (hint to the Informix people, } > please say something about this)? } > } > If there is a bug, I want to know about it, since it will be bad news } > for me, but I am wondering if there really is one or not. } > } > } > Thanks, } > } > Bill MacLean } } I was recently "bitten" by an Informix outer join problem running under } V4.0 - I'm not sure if its a bug, or handled differently in newer releases. } I was trying to use an outer join to find keys in table "B" which did not } exist in table "A", without using NOT IN or NOT EXISTS. The query looked } something like: } } select A.colA, B.colB from B, outer A } where A.colA = B.colB and A.colA is null; } } The problem is in the "A.colA is null" part, and the query returns both the } normal join items, and the outer items, but colA is null for ALL returned } records! } } I'm pretty sure I've used this technique in Oracle without problems, } although I didn't go back to verify it. } } -Peter Sylvester } -- regards, +----------------------------------------------------------------------------+ | . . | Bob Baskett | | ... ... | Software Engineer | | ..... ..... | Business Systems Integration Group | | .. ... .. | Semiconductor Products Sector | | . . . | Mesa, AZ | | | President, Informix Users Group Of Arizona | | Motorola, Inc. | | +----------------------------------------------------------------------------+ | Sun 690MP 4.1.3, Online 5.01, 4.10.UD1 Tools, Fourgen v4.10.UC1 | +----------------------------------------------------------------------------+