Re: SQL-92 Outer Join Support (lack)

There is an outer join (at least for 8.0).

The operator is a plus sign enclosed in parentheses (+), and is placed on the "side' of the join that is deficient in information.

ie
select e.last_name, e.id, c.name
from s_emp s, s_customer c
where e.id(+) = c.sales_rep_id;

will give a list of all customer names and incude the sales rep name & id where one exists.

Mike Rowland <102622.2522_at_CompuServe.COM> wrote in article <eejAlXe28GA.186_at_ntdwwaaw.compuserve.com>...
> I have recently moved back to using Oracle 7.3 at a client. I
> have grown accustomed to using the SQL-92 syntax for outer joins.
> Oracle 7.3 has several limitations that prevent these from
> working. Does anyone have "good" work arounds for these?
> 1. A left join that includes a literal selection?
> SQL-92 example for "HOME" phone number:
> SELECT PERSON.NAME, PHONE.TYPE, PHONE.NUMBER
> FROM PERSON LEFT OUTER JOIN PHONE ON
> PHONE.SSN = PERSON.SSN AND PHONE.TYPE = 'HOME'
> WHERE PERSON.STATUS = 'ACTIVE'
> ORACLE 7.3.3 EQUIVALENT? (Without using a view like "HOME_PHONE")
> 2. Needing to list all a PERSON's available information
> SQL-92 example:
> SELECT PERSON.NAME, PHONE.NUMBER, ADDRESS.CITY, DEPENDANT.NAME
> FROM PERSON LEFT OUTER JOIN PHONE ON
> PHONE.SSN = PERSON.SSN
> LEFT OUTER JOIN ADDRESS ON
> ADDRESS.SSN = PERSON.SSN
> LEFT OUTER JOIN DEPENDANT ON
> DEPENDANT.PARENT_SSN = PERSON.SSN AND DEPENDANT.STATUS = 'ACTIVE'
> WHERE PERSON.STATUS = 'ACTIVE'
> ORACLE 7.3.3 ONLY ALLOWS AN OUTER JOIN TO ONE TABLE. HELP??
> (Chaining them will leave out information if a link is missing)
> Thanks in Advance, Mike Rowland MRowland1_at_compuserve.com
> Mike Rowland MRowland1_at_compuserve.com
>
> --
> *****************************************************************
> * The opinions expressed are solely the opinion of Mike Rowland *
> * and not those of Cap Gemini America. *
> *****************************************************************
>
Received on Thu Oct 16 1997 - 00:00:00 CEST