Re: natural join and outer join

eh936-at-yahoo.com wrote:

> can someone give me a really simple easy to understand example of each of
> the useage and meaning (what is happening) of these two types of joins ?

DB2 for LUW doesn't have NATURAL JOINs. But in theory, a natural join is
the same as an equi-join ], automatically picking the columns from both
tables that have the same column names.

For example, joining two relations R1(a, b, c, d, e) and R2(a, d, g, h, i).
The following two join clauses are identical:

r1 NATURAL JOIN r2

SELECT r1.a, r1.b, r1.c. r1.d, r1.e, r2.g, r2.h, r2.i
FROM r1 INNER JOIN r2 ON
( r1.a = r2.a AND r1.d AND r2.d )

Note that a natural join does eliminate duplicates for the A and D columns,
which occur in both tables. Only one column A and one column D remains.
That's why I wrote a sub-select above. (Note that a sub-select just
returns a table - as does a join.)

An outer-join is explained in the DB2 manuals. Essentially, it does a join
as shown in the 2nd example above, but it also retains the rows from a
table if there is no matching row in the other table.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany