Oracle 中的左连和右连

An Oracle SQL outer join differs from a natural join because it includes non-matching rows.  Oracle SQL has several joins syntax variations for outer joins.

Outer join (+) syntax examples

The most common notation for an outer join is the (+) notation.  This, from the great book "Easy Oracle SQL" by Lt. Col. John Garmany:

For example, if I list my authors and the books they have written, I get the results below.

SQL> select

  2    author_last_name,

  3    book_key

  4  from

  5    author join book_author using (author_key)

  6  order by author_last_name;

 
 

AUTHOR_LAST_NAME                         BOOK_K

---------------------------------------- ------

hester                                   B101

hester                                   B109

hester                                   B116

jeckle                                   B102

 . . .  

The problem with this listing is that there are ten authors in the author table and only eight listed.  The remaining two authors have not yet written a book. What if I wanted the listing to include these two authors?  Because they do not match the equal join, I will need to use an outer join.  An outer join will include all rows from the outer table and those matching rows from the other table.  Rows that are included from the outer table that have no match in the other table will have NULLs in those columns.

SQL> select

  2    author_last_name,

  3    book_key

  4  from

  5    author left outer join book_author using (author_key)

  6  order by author_last_name;   

In the example above, the AUTHOR table is on the left, and we are using a left outer join, so we get all the rows in the AUTHOR table and the matching rows in the book_author table.  Notice that both authors clark and mee now are listed, and the book_key column is NULL.  In the standard Oracle format, outer joins can be confusing.  Below is the same query in the standard Oracle format.

select

  author_last_name,

  book_key

from

  author,

  book_author

where

  author.author_key = book_author.author_key(+)

order by author_last_name;

Notice the (+) in the WHERE clause.  This indicates a left outer join.  If we were using a right outer join, the WHERE clause would be:

author.author_key(+) = book_author.author_key

Here, all the rows from the book_author table would be included and the missing rows from the AUTHOR table would be NULL.  The example below demonstrates the ANSI right outer join.

SQL> select

  2    author_last_name,

  3    book_key

  4  from

  5    author right outer join book_author using (author_key)

  6  order by author_last_name;

 

AUTHOR_LAST_NAME                         BOOK_K

---------------------------------------- ------

hester                                   B116

hester                                   B109

hester                                   B101

=======================================================================

=======================================================================

I Believe that the *= is non ansi standard on doing joins. Oracle uses (+). This is place next to the column. So

a.foo = b.foo(+) would do a left outer join
a.foo(+) = b.foo woudl do a right 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' join

create 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.

=======================================================================

=======================================================================