Monday, January 31, 2011

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.