Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Mysterious Join Results...!

Mysterious Join Results...!

From: <mccmx_at_hotmail.com>
Date: 25 Nov 2005 03:16:58 -0800
Message-ID: <1132917418.487429.132860@g47g2000cwa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US