René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Outer Join with Oracle SQL | ||
A traditional outer join
A "normal" join finds values of two tables that are in a relation to each other. In most cases, this relation is
equality (=), but it can also be all sorts of operations that either return true or false. The important thing is
that a "normal" join only returns rows of both tables of which the compared columns return true.
Of course, a row whose column-value is not found in the other table's joined column is not returned at all.
However, sometimes, there is a requirment to show these rows as well.
Consider the following example. It consists of two tables l (being the left table) and r (being the
right table). The left table consists of five rows, which assigns english names to the numbers 1 through 5.
create table l ( i number primary key, v varchar2(20) ); insert into l values (1, 'one' ); insert into l values (2, 'two' ); insert into l values (3, 'three'); insert into l values (4, 'four' ); insert into l values (5, 'five' );
The right table contains translations of these numbers in different languages ('de' for german, 'fr' for french and
'es' for spanish). However, not every number is translated, the number 4 is only translated into french (=quattre) and
the number 5 only into spanish (=cinco). And more importantly, the number 1 is not translated at all.
create table r ( i number references l, v varchar2(20), l char(2), primary key (i,l) ); insert into r values (2, 'zwei','de'); insert into r values (2, 'deux','fr'); insert into r values (2, 'dos' ,'es'); insert into r values (3, 'drei','de'); insert into r values (4, 'quattre','fr'); insert into r values (4, 'cuatro','es'); insert into r values (5, 'cinco','es'); Querying the german translations
Now, we want to query the german translations of the numbers. A wrong
approach would be to use a normal join:
select l.v "English", r.v "German" from r,l where l.i = r.i and r.l = 'de';
This approach is wrong because not each number has a german counterpart in the right table,
resulting in the following result set:
English German -------------------- -------------------- two zwei three drei
This might be what one want or it might not. Assuming that we want to return all numbers, even
if the german translation is missing, we need an outer join. An outer join uses a (+) on the side
of the operator (which in this case happens to be the equality operator) where we want to have nulls
returned if no value matches:
select l.v "English", r.v "German" from r,l where l.i = r.i (+) and r.l(+) = 'de';
And this returns a row for each english word, even if there is no german translation:
English German -------------------- -------------------- one two zwei three drei four five
The following example does more or less the same, except that it select french and german
translations:
select l.v "English", nvl(r.v,'--') "Translation", nvl(r.l,'--') "Language" from l, (select i,v,l from r where r.l= 'de' or r.l= 'fr' ) r where l.i=r.i(+);
Here's what it returns:
English Translation La -------------------- -------------------- -- one -- -- two zwei de two deux fr three drei de four quattre fr five -- -- Housekeeping
Cleaning up the mess...
drop table r; drop table l; A 'left right' joincreate table table1 ( key number (1), value1 number (4) ); create table table2 ( key number (1), field char (1), value2 number (4) ); insert into table1 values (1, 1000); insert into table1 values (2, 1000); insert into table2 values (1, 'A', 200); insert into table2 values (1, 'B', 300); insert into table2 values (1, 'C', 50); insert into table2 values (3, 'A', 60);
Doing the 'left right' select statement:
select distinct * from ( select t1.key, t2.field, t1.value1, t2.value2 from table1 t1 left join table2 t2 on t1.key = t2.key union select t1.key, t2.field, t1.value1, t2.value2 from table1 t1 right join table2 t2 on t1.key = t2.key);
This results in:
KEY F VALUE1 VALUE2 ---------- - ---------- ---------- 1 A 1000 200 1 B 1000 300 1 C 1000 50 2 1000 A 60
Cleaning up...
drop table table1; drop table table2; Thanks
Thanks to Alejandor Ramírez Aldariz who spottet and corrected two errors.
|