Re: Mysterious Join Results...!

<mccmx_at_hotmail.com> wrote in message
news:1132917418.487429.132860_at_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.
>

This is correct according to the syntax definition.

In the first case, the "d.loc = 'DALLAS'" is part of the join condition, so it is 'outered', so some preserved rows with nulls appear.

In the second case, the join is only on
'e.dept_no = d.dept_no', which produces a set including some 'outered' rows, with null values for d.loc. The predicate d.loc is then applied to this result set, so those rows disappear.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005