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

lambert@mitre.org (David W. Lambert) wrote: >In article <3na60a$6ff@ixnews3.ix.netcom.com>, bmaclean@ix.netcom.com (Bill >MacLean) wrote: >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; Maybe I'm wrong, but I thought it was normal practise to use a not in sub query to handle such a problem and not an outer join. Is'nt that the relational operator for difference? I would have written the query this way: select colA from A where colA not in (select distinct colA from B); >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 gordonh@acslink.net.au RIPLEY, Queensland, Australia