Re: outer join question

matt lewis wrote:
> Ok Brian, I'm fine with the (+) operator and understand it's placement
> but I don't understand the ANSI equivalent 'right' or 'left' keywords.
> The book is very ambiguous. If I see the operator (+) next to the
> table.column what is the way or rule to decide whether it's right or left?
(+) marks the column that is the inner (null producer) FROM T, S WHERE T.c1 = S.c1 (+) would be a LEFT outer join FROM S, T WHERE T.c1 = S.c1 (+) is a RIGHT OUTER join.

In both cases S is the inner. And T is the outer. In the first case T is placed LEFT of S. In the second it is RIGHT of S.

Oftentimes developers only use LEFT OUTER joins. That is they simply flip the tables around for consistency:

SELECT T LEFT OUTER JOIN S ON T.c1 = S.c1 SELECT S RIGHT OUTER JOIN T ON T.c1 = S.c1

The main benefits of this notation is that the join property is specified only once and the join condition is separated from the post join WHERE clause.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab