On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, June 7, 2008

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);

No comments:

Post a Comment