Re: Ansi Outer Joins via ODBC

Well, it supports left outer joins and right outer joins, true enough -and it uses the plus operator to indicate that, also true. But not until 9i does it support full outer joins, short of using the union operator. And not until 9i does it support any type of outer join with a half-decent ANSI-compliant syntax that muggins here can remember off the top of his head.

That's what I meant to say, anyway.... I saw the words 'outer joins' and assumed they read 'full outer joins'. It *was* 1 in the morning at the time!!

Regards
HJR "Scott Imlach" <Imlachws_at_resdat.com> wrote in message news:u6jfvflph4er8d_at_corp.supernews.com...
> Oracle has supported the outer join for some time actually. The syntax
uses
> the (+) symbol in the where clause associated with the column who's source
> table you wish to be in the outer side of the join. You may wish to run
the
> following to verify:
>
>
> create table tab1
> (col1 number not null,
> col2 char(1) null
> );
>
> create table tab2
> (col1 number not null,
> col2 char(1) not null
> );
>
> insert into tab1 (col1, col2) values (1,'A');
> insert into tab1 (col1, col2) values (2,'B');
> insert into tab1 (col1, col2) values (3,'C');
>
> insert into tab2 (col1, col2) values (1,'Z');
> insert into tab2 (col1, col2) values (3,'X');
> insert into tab2 (col1, col2) values (4,'Y');
>
> select tab1.col1, tab1.col2, tab2.col1, tab2.col2
> from tab1, tab2
> where tab1.col1 (+) = tab2.col1;
>
> /*
> COL1 C COL1 C
> --------- - --------- -
> 1 A 1 Z
> 3 C 3 X
> 4 Y
> */
>
> select tab1.col1, tab1.col2, tab2.col1, tab2.col2
> from tab1, tab2
> where tab1.col1 = tab2.col1 (+);
>
> /*
> COL1 C COL1 C
> --------- - --------- -
> 1 A 1 Z
> 2 B
> 3 C 3 X
> */
>
> select tab1.col1, tab1.col2, tab2.col1, tab2.col2
> from tab1, tab2
> where tab1.col1 = tab2.col1;
>
> /*
> COL1 C COL1 C
> --------- - --------- -
> 1 A 1 Z
> 3 C 3 X
> */
>
> drop table tab1;
> drop table tab2;
>
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:3c691aaf$0$12799$afc38c87_at_news.optusnet.com.au...
> > Oracle doesn't do outer joins, full stop. (Period, if you insist). Not
> > until 9i, anyway. Before then, you need to investigate the use of the
> Union
> > keyword.
> >
> > In either event, it's got sod-all to do with ODBC, server or client
> > versions.
> >
> > Regards
> > HJR
> >
> >
> > "Peter K" <P.Kloo_at_mylius.com> wrote in message
> > news:a4ausj$1e08pe$1_at_ID-43420.news.dfncis.de...
> > > Hi,
> > > what configuration (db-version, odbc-driver version) is necessary to
> > process
> > > outer joins in ANSI-syntax via ODBC ?
> > > (server machine win2k, client nt4/ 2k)
> > > Regards,
> > > Peter
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Tue Feb 12 2002 - 21:29:49 CST