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>
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
--------- ---------
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 40Received on Thu Oct 03 2002 - 11:01:30 CEST