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

}From: lambert@mitre.org (David W. Lambert) }Date: 26 Apr 1995 11:57:44 GMT }X-Informix-List-Id: <news.13375> }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! This is exactly an instance of what I documented -- there is a filter condition on the outer table, which means that all rows in the dominant (inner) table are selected. This is the defined behaviour for Informix OUTER joins, and is correct. It is not a bug until the definition of outer joins under Informix is corrected. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>