Home -> Usenet -> comp.databases.oracle -> outer join behaviour

outer join behaviour

From: konghou <brotherhou_at_yahoo.com>
Date: 3 Oct 2002 02:01:30 -0700
Message-ID: <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 - 11:01:30 CEST












Original text of this message