Multiple outer joins

There has been some dispute related to an original post about extending the SQL language with alternate where clauses. I offered a solution that used multiple outer joins to do the same thing as the test example, but someone claimed that it would not work, but rather produce integer multiples of the correct results.

I have recently retested the behavior of multiple outer joins against the same base table and am pleased to announce that this is not correct, at least in Oracle.

For example, I have a item table, a item_vendor relationship table, and a vendor table.

Item -----> Item_Vendor <---- Vendor

If I do the following query, it returns the correct results, regardless of missing items or vendors.

Select A.part_no, B.item_desc, C.vendor_name  from Item_Vendor A, Item B, Vendor C
  where A.part_no = B.part_no (+) and

        A.vend_no = C.vend_no (+);

I would think that outer joins would be nearly useless without this behavior.

• Mark.