Re: outer joins

yoosh_at_cs.ualberta.ca (Yoo-Shin Lee) wrote:

>Hi,
 

>anyone know of any way to do an outer join (full) in oracle?
Put (+) behind the column-name from the table you want to be "optional". Be sure when you have more than one equation using the two joined tables to use the (+)-operator in all of these equations.

Like this:

SELECT A.SOME_INFO
,      B.SOME_OTHER_INFO
WHERE  A.B_ID = B.ID(+)
AND    A.SOME_DATE < B.DATE(+)

This gives a list of all rows in A even if there is no row in B (the B.SOME_OTHER_INFO will be NULL). Notice you can also use the (+) with a <-operator.
If you have any other restrictions on B. Like B.DATE > SYSDATE, I think you cannot use the (+)-operator, because you can use this only in the join-conditions. You could use NVL(B.DATE, SYSDATE+1) > SYSDATE in stead to get Oracle to accept any rows where B is outer-joined. The B.DATE will be NULL with a outer-join and the NVL will cause the condition to be true.

>--
> Yoo-Shin Lee
> yoosh_at_cs.ualberta.ca
> http://web.cs.ualberta.ca/~yoosh

Bye,
Wilfred
The Netherlands Received on Mon Nov 27 1995 - 00:00:00 CET