outer joins
Yoo-Shin Lee
anyone know of any way to do an outer join (full) in oracle?
--
Yoo-Shin Lee
yo...@cs.ualberta.ca
http://web.cs.ualberta.ca/~yoosh
Roeland Loggen
>Hi,
>anyone know of any way to do an outer join (full) in oracle?
Oracle supports the standard (?) outer join syntax of SQL:
Select whatever
From table a, table b
where a.primary key += b.foreign key
Hope this helps,
Roeland
Ps: you can also switch the += to =+ (outer join over b)
--
// ===========================================================================
// Roeland Loggen (rl...@ctp.com) After being built and asked "is there a god"
// Cambridge Technology Partners the supercomputer replied "well......
// Cambridge MA (617)-374-8788 there wasn't one, but now there is"
Randy Dewoolfson
: Hi,
: anyone know of any way to do an outer join (full) in oracle?
: --
: Yoo-Shin Lee
: yo...@cs.ualberta.ca
: http://web.cs.ualberta.ca/~yoosh
add the (+) operator on the column you wish to be considered inclusively
--
..uu. ----------------------
.?$" '?i . I Randy DeWoolfson I
.T^M .@" d9 . f ,.un. b, i I--------------------I
" Z :#" M `8 U < .dP"``"# `M @" I ran...@cais.com I
&H?` Xl _R $5. $ ?* @ 'P,#" I--------------------I
,d#^*L :RP'~$b f`$L:M Xf .f' dH` I ,\//. I
& 'M ,P `E M "$ Mux~ n!` I |o o| I
dk `h" ' j " y" *~ I====oOO==(_)==Ooo===I
W. van der Deijl
>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
> yo...@cs.ualberta.ca
> http://web.cs.ualberta.ca/~yoosh
Bye,
Wilfred
The Netherlands
Martin Farber
That's only a left (or right) outer join. The only way right now to do a full
(symmetric) outer join in Oracle is to do a left-outer UNION the right-outer!
It's no pretty, but it's a living! ;^)
Sincerely,
Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."
| NYNEX Science & Technology
| 500 Westchester Ave, Rm 1B-23
| White Plains, NY 10604
| -----------------
| email: far...@nynexst.com
| Voice: 914/644-2656
| FAX: 914/644-2216
In article d...@zippy.cais.net, ran...@cais3.cais.com (Randy Dewoolfson) writes:
>Yoo-Shin Lee (yo...@cs.ualberta.ca) wrote:
>: Hi,
>
>: anyone know of any way to do an outer join (full) in oracle?
>
>
>: --
>: Yoo-Shin Lee
>: yo...@cs.ualberta.ca
>: http://web.cs.ualberta.ca/~yoosh
>
>