Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ANSI joins not working...
David Portas schrieb:
>>I'm wondering how yhour code:
>>
>> select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES
>> from PORTFOLIO_MASTER p left outer join PORTFOLIO_HOLDINGS h
>> on p.PORTFOLIO_CODE = h.PORTFOLIO_CODE
>> and h.INSTRUMENT_CODE = 'AFI'
>>
>> select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES
>> from PORTFOLIO_MASTER p full outer join PORTFOLIO_HOLDINGS h
>> on p.PORTFOLIO_CODE = h.PORTFOLIO_CODE
>> and h.INSTRUMENT_CODE = 'AFI'
>>
>>differs from the OPs original ANSI code:
>>
>> select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES
>> from PORTFOLIO_MASTER p left outer join PORTFOLIO_HOLDINGS h
>> on p.PORTFOLIO_CODE = h.PORTFOLIO_CODE
>> where h.INSTRUMENT_CODE = 'AFI'
>>
>> select p.PORTFOLIO_CODE, h.HOLDING_NUMBER_SHARES
>> from PORTFOLIO_MASTER p full outer join PORTFOLIO_HOLDINGS h
>> on p.PORTFOLIO_CODE = h.PORTFOLIO_CODE
>> where h.INSTRUMENT_CODE = 'AFI'
>>
>>as I don't see a difference. This is the same code the OP is reporting
>>as 'not working'
> > > Not so. In his ANSI queries Peter referenced the unpreserved tables in > the WHERE clause. So his queries are logically equivalent to an INNER > join. My version moved that predicate to the ON clause, which makes a > big diffrence to the meaning of the query. > > -- > David Portas >
I would be very careful with full outer join and putting the query predicate to the ON clause - you are joining actually on modified condition. The result might not be that you expect.Directly you can not compare the old (+) syntax with the full outer join - before ANSI syntax Oracle hadn't outer join. So , consider this:
SQL> set pages 999 SQL> set lines 200 SQL> col dname for a15 SQL> col ename for a15 SQL> SELECT d.deptno,d.dname,e.ename,e.sal
2 FROM dept d FULL OUTER JOIN emp e
3 ON d.deptno=e.deptno AND e.sal < 2000
4 /
DEPTNO DNAME ENAME SAL ---------- --------------- --------------- ---------- 20 RESEARCH SMITH 800 30 SALES ALLEN 1600 30 SALES WARD 1250 30 SALES MARTIN 1250 30 SALES TURNER 1500 20 RESEARCH ADAMS 1100 30 SALES JAMES 950 10 ACCOUNTING MILLER 1300 40 OPERATIONS JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000
15 Zeilen ausgewählt.
SQL> Best regards
Maxim Received on Tue Jun 14 2005 - 14:37:24 CDT