Re: Ansi Outer Joins via ODBC

From: Scott Imlach <Imlachws_at_resdat.com>
Date: Tue, 12 Feb 2002 16:22:53 -0900
Message-ID: <u6jfvflph4er8d@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 - 19:22:53 CST