Outer Joins

rodney says: >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 > >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). > Informix 4GL Reference Manual Appendix G says: "Any dominant-table rows that do not have a matching row from the subservient table receive a row of NULL values in place of a subservient-table row." (Table 'A' is the dominant-table and 'B' is the subservient.) 'BOB' is there for the same reason RODNEY and JOE are: "Rows in the dominant table are retrieved without considering the join, but rows from the subservient table are retrieved only if they satisfy the join condition". The join condition here includes: (bYN is NULL or bYN = "Y"), therefore b.yn = "Y" for BILLY. Informix devotes 14 pages to the discussion of outer joins, obviously for good reasons. The question remains: How can two or more "SQL compliant" RDMSs retrieve different rows? || ======= **** **T* ======= ||John Regep || **** **** ||Store Systems Developement || **** *R** ||Kmart Corporation || =========== ********* =========== ||3100 West Big Beaver || ***A* **** ||Troy, MI 48084 || **** **** ||(810) 643-5078 ||=============== *M** **** ===============||uunet!kmart!jregep