Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Mysterious Join Results...!
Oracle 9.2.0.2 RHEL 4 Linux (Itanium).
I have picked up on a subtle difference in the new 9i Join syntax. It appears that there are 2 forms of the join command which seem logically the same, but return different results:
In the 2 statements below, the only difference is that I have changed the keywork 'AND' to 'WHERE' on line 3....
Why does this return different results..? It looks as if the additional filter (d.loc='DALLAS') is being applied at different times.
1 select e.ename, d.deptno from emp e right outer join dept d on
2 e.deptno = d.deptno
3* and d.loc = 'DALLAS'
SQL> /
ENAME DEPTNO
---------- ----------
10 SMITH 20 JONES 20 SCOTT 20 ADAMS 20 FORD 20 30 40
8 rows selected.
Elapsed: 00:00:00.00
SQL> ed
Wrote file afiedt.buf
1 select e.ename, d.deptno from emp e right outer join dept d on
2 e.deptno = d.deptno
3* where d.loc = 'DALLAS'
SQL> /
ENAME DEPTNO
---------- ----------
SMITH 20 JONES 20 SCOTT 20 ADAMS 20 FORD 20
5 rows selected. Received on Fri Nov 25 2005 - 05:16:58 CST
![]() |
![]() |