Outer Joins (was Re: Outer Join Bug (was It's Incredible))
David W. Lambert <lambert@mitre.org> wrote (edited):
>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 think I've run into the same thing, but I'm not sure, so here's my
situation (these are data samples -- some of the data are meaningful,
others are just there to show that there is more data than what I'm
showing):
Table A
-------
aID aDesc
--- -----
3 RODNEY
3 JOE
4 BOB
20 BILLY
Table B
-------
bID bYN
--- ----
1 Y (record not related to this case)
2 Y (record not related to this case)
4 N
20 Y
Here's what I want: All aDesc whose ID's are not in Table B, plus
any aDesc whose ID's are in Table B and have a "Y" in bYN.
Specifically, in the case above, I want RODNEY, JOE (because they
are not in Table B) and BILLY (because he's a "Y" in Table B), but
I don't want BOB (because he's a "N" in Table B).
If you do
SELECT A.*,B.*
FROM A, OUTER B
WHERE aID = bID
you get:
aID aDesc bID bYN
3 RODNEY
3 JOE
4 BOB 4 N
20 BILLY 20 Y
which is exactly what I'd expect, but when you do
SELECT A.*,B.*
FROM A, OUTER B
WHERE aID = bID
and (bYN is NULL or bYN = "Y")
you get
aID aDesc bID bYN
3 RODNEY
3 JOE
4 BOB
20 BILLY 20 Y
which is _not_ what I would expect (I don't know why BOB is in there,
and further, why BOB's OUTER fields are NULL).
Now, when I came across this, I just figured that it was not legal
to put a WHERE condition on an OUTER field, so I ended up doing a
NOT IN that was UNIONed to an OUTER, and I get the answer I want.
After seeing all this talk, though, I'm curious. Is it illegal to
do as above? Is this the intended behavio[u]r?
Finally, this is done with DB-Access Version 5.00.UC3.
Thanks,
rodney