Understand outer join by examples

SQL> create table p (pid number, pname varchar2(30));

Table created.

SQL> alter table p add constraint p_pk primary key (pid);

Table altered.


SQL> create table c (cid number, cname varchar2(30), pid number,
  2  constraint c_pk primary key (cid),
  3  constraint c_p_fk foreign key(pid) references p(pid));

Table created.

SQL> create index c_n1 on c(pid);

Index created.

SQL> select * from p;

       PID PNAME
---------- -----
         1 a
         2 b
         3 c
         0 e

SQL> select * from c;

       CID CNAME        PID
---------- ----- ----------
        11 A              1
        12 B              2
        22 BB             2
        14 DD

SQL> select p.*, c.* from p,c where p.pid=c.pid;

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         1 a             11 A              1
         2 b             12 B              2
         2 b             22 BB             2

SQL> select p.*,c.* from p,c where p.pid=c.pid(+);

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         1 a             11 A              1
         2 b             12 B              2
         2 b             22 BB             2
         3 c
         0 e

SQL> select p.*,c.* from p,c where p.pid=c.pid(+) and c.cname='B';

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         2 b             12 B              2

SQL> select p.*,c.* from p,c where p.pid=c.pid(+) and c.cname(+)='B';

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         1 a
         2 b             12 B              2
         3 c
         0 e

SQL> select p.*,c.* from p,c where p.pid=c.pid and c.cname(+)='B';

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         2 b             12 B              2

SQL> select p.*,c.* from p,c where p.pid>=c.pid(+);

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         3 c             12 B              2
         3 c             22 BB             2
         3 c             11 A              1
         2 b             12 B              2
         2 b             22 BB             2
         2 b             11 A              1
         1 a             11 A              1
         0 e

8 rows selected.

SQL> select p.*,c.* from p,c where p.pid>=c.pid;

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         1 a             11 A              1
         2 b             11 A              1
         3 c             11 A              1
         2 b             12 B              2
         3 c             12 B              2
         2 b             22 BB             2
         3 c             22 BB             2

7 rows selected.

SQL> select p.*,c.* from p,c where p.pid>c.pid;

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         2 b             11 A              1
         3 c             11 A              1
         3 c             12 B              2
         3 c             22 BB             2

SQL> select p.*,c.* from p,c where p.pid>c.pid(+);

       PID PNAME        CID CNAME        PID
---------- ----- ---------- ----- ----------
         3 c             12 B              2
         3 c             22 BB             2
         3 c             11 A              1
         2 b             11 A              1
         1 a
         0 e

6 rows selected.