Outer Join

Column Key_L  Format 999999 Heading Key-L
Column Key_R  Format 999999 Heading Key-R
Column Join_type Format A20 Heading " "
Break On Join_type

Drop Table Table_L;
Drop Table Table_R;

Create Table Table_L (Key_L Number(1));
Create Table Table_R (Key_R Number(1));

Insert Into Table_L Values (1);
Insert Into Table_L Values (2);

Insert Into Table_R Values (1);
Insert Into Table_R Values (3);

Select * from Table_L;
Select * from Table_R;

Prompt =========================================================
Prompt = Non-Ansi Joins
Prompt =========================================================

Define Join_type = "'Key_L = Key_R'"

Select &Join_Type as Join_type, KEY_L, KEY_R
  From Table_L L 
     , Table_R R
 Where L.Key_L = R.Key_R;

Define Join_type = "'Key_L(+) = Key_R'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L L
     , Table_R R
 Where L.Key_L(+) = R.Key_R;

Prompt
Prompt =========================================================
Prompt = Ansi-Compliant Joins
Prompt =========================================================

Define Join_type = "'Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L 
  Join Table_R 
   On ( Key_L = Key_R);

Define Join_type = "'Right Outer Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L Right Outer 
  Join Table_R 
   On ( Key_L = Key_R);

Define Join_type = "'Full Outer Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L Full Outer 
  Join Table_R 
   On ( Key_L = Key_R);