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