FULL OUTER JOIN LOOP


>>Script Language and Platform: Oracle

In one report, I have to display the matched records from tables A and B, unmatched records from table A, unmatched records from table B–similar to a full outer join.

Due to some constraints I can not use a full outer join on my two large tables.

Author: JP Vijaykumar



FULL OUTER JOIN LOOP
Author JP Vijaykumar Oracle DBA
Date 04-12-2009
/*
In reporting, I have to join two very large table through a full outer join.
For some reason, I have to avoild full outer join on the two large tables.
What are my options? I simulated this test case scenario.
*/
create table temp_jp1(col1 number, col2 varchar2(20)) tablespace users;
create table temp_jp2(col1 number, col2 varchar2(20)) tablespace users;
insert into temp_jp1 values(1,’ram’);
insert into temp_jp1 values(2,’sita’);
insert into temp_jp1 values(4,’lakshman’);
insert into temp_jp1 values(6,’bharath’);
insert into temp_jp1 values(8,’satrughnu’);
insert into temp_jp2 values(1,’ram’);
insert into temp_jp2 values(3,’krishna’);
insert into temp_jp2 values(4,’urmila’);
insert into temp_jp2 values(5,’radha’);
insert into temp_jp2 values(7,’satyabhama’);
insert into temp_jp2 values(9,’mira’);
Commit;

select * from temp_jp1;

COL1 COL2
———- ——————–
1 ram
2 sita
4 lakshman
6 bharath
8 satrughnu

5 rows selected.

select * from temp_jp2;

COL1 COL2
———- ——————–
1 ram
3 krishna
4 urmila
5 radha
7 satyabhama
9 mira

6 rows selected.

/*
Using an equi join I get this output.
*/

select a.col1,a.col2,b.col2
from temp_jp1 a, temp_jp2 b
where a.col1 = b.col1;

COL1 COL2 COL2
———- ——————– ——————–
1 ram ram
4 lakshman urmila

2 rows selected.

/*
Now a full outer join on the two tables will give the following output.
*/

select a.col1,nvl(a.col2,’ *** ‘) col2,nvl(b.col2,’ *** ‘) col2
from temp_jp1 a, temp_jp2 b
where a.col1 = b.col1(+)
union
select b.col1,nvl(a.col2,’ *** ‘),nvl(b.col2,’ *** ‘)
from temp_jp1 a, temp_jp2 b
where a.col1(+) = b.col1;

COL1 COL2 COL2
———- ——————– ——————–
1 ram ram
2 sita ***
3 *** krishna
4 lakshman urmila
5 *** radha
6 bharath ***
7 *** satyabhama
8 satrughnu ***
9 *** mira

9 rows selected.

/*
I wrote the following script to simulate a full outer join on my two tables.
*/

SQL> declare
2 v_num number;
3 v_str varchar2(20);
4 begin
5 for c1 in (select col1,col2 from temp_jp1) loop
6 v_num :=0;
7 select count(*) into v_num from temp_jp2 where col1 = c1.col1;
8 if (v_num > 0) then
9 select col2 into v_str from temp_jp2 where col1 = c1.col1;
10 dbms_output.put_line(c1.col1||’ ‘||c1.col2||’ ‘||v_str);
11 else
12 dbms_output.put_line(c1.col1||’ ‘||c1.col2||’ **** ‘);
13 end if;
14 end loop;
15 for c2 in (select col1,col2 from temp_jp2) loop
16 v_num :=0;
17 select count(*) into v_num from temp_jp1 where col1 = c2.col1;
18 if (v_num > 0) then
19 null;
20 else
21 dbms_output.put_line(c2.col1||’ **** ‘||c2.col2);
22 end if;
23 end loop;
24 end;
25 /
1 ram ram
2 sita ****
4 lakshman urmila
6 bharath ****
8 satrughnu ****
3 **** krishna
5 **** radha
7 **** satyabhama
9 **** mira

PL/SQL procedure successfully completed.

/*
The col1 is not in sorted order. I re-created my script with different logic.
*/

declare
v_str1 varchar2(100);
v_str2 varchar2(100);
v_count number;
v_max number;
begin
select case when a.max > b.max then a.max else b.max end into v_max
from (select max(col1) max from temp_jp1) a,
(select max(col1) max from temp_jp2) b;

for i in 1..v_max loop
begin
v_str1:=”;
v_str2:=”;
v_count:=0;
select count(1) into v_count from temp_jp1 where col1 = i;

if (v_count > 0) then
select col2 into v_str1 from temp_jp1 where col1 = i;
end if;
v_count:=0;
select count(1) into v_count from temp_jp2 where col1 = i;

if (v_count > 0) then
select col2 into v_str2 from temp_jp2 where col1 = i;
end if;

if ((v_str1 is not null ) and (v_str2 is not null )) then
v_str1:=v_str1||’|’||v_str2;
elsif ((v_str1 is not null ) and (v_str2 is null )) then
v_str1:=v_str1||’|*********’;
elsif ((v_str1 is null) and (v_str2 is not null )) then
v_str1:=’*******|’||v_str2;
end if;

if (v_str1 is not null) then
dbms_output.put_line(i||’ ‘||v_str1);
end if;

exception
when others then
null;

end;
end loop;
end;

1 ram|ram
2 sita|*********
3 *******|krishna
4 lakshman|urmila
5 *******|radha
6 bharath|*********
7 *******|satyabhama
8 satrughnu|*********
9 *******|mira

PL/SQL procedure successfully completed.

Happy scripting.




Disclaimer:
We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home

Latest Articles