Re: outer join behaviour

I guess you need the answer for the last SQL only.

b.empno(+) = 7782;

It tells Oracle to give us all the deptno that either match 7782 or not. So the output make sense.

brotherhou_at_yahoo.com (konghou) wrote in message news:<c19678a1.0210030101.3ddeaab9_at_posting.google.com>...
> Following are some outer join behaviour worth discussing.
>
> SQL> select a.deptno, b.empno from dept a, emp b where
> a.deptno=b.deptno(+);
>
> DEPTNO EMPNO
> --------- ---------
> 10 7782
> 10 7839
> 10 7934
> 20 7369
> 20 7876
> 20 7902
> 20 7788
> 20 7566
> 30 7499
> 30 7698
> 30 7654
> 30 7900
> 30 7844
> 30 7521
> 40
>
> 15 rows selected.
>
> SQL> select a.deptno, b.empno from dept a, emp b where
> a.deptno=b.deptno(+) and b.empno is null;
>
> DEPTNO EMPNO
> --------- ---------
> 40
>
> SQL> select a.deptno, b.empno from dept a, emp b where
> a.deptno=b.deptno(+) and b.empno = 7782;
>
> DEPTNO EMPNO
> --------- ---------
> 10 7782
>
> SQL> select a.deptno, b.empno from dept a, emp b where
> a.deptno=b.deptno(+) and b.empno(+) = 7782;
>
> DEPTNO EMPNO
> --------- ---------
> 10 7782
> 20
> 30
> 40
Received on Thu Oct 03 2002 - 22:33:00 CEST