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

> > 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! If colA where not null for all returned records then there would indeed be a bug. In your select statement you have specifically asked for only those rows where colA is null. Of course this means that colB would be NULL too. puzzled j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "A thing is bigger for being shared" - Gaelic _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________