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