Re: outer joins with composite keys in oracle impossible?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 Jan 1999 17:35:53 GMT
Message-ID: <36b64e04.15194808@192.86.155.100>

A copy of this was sent to rosinowski_at_gmx.de (Jan Rosinowski) (if that email address didn't require changing) On Wed, 27 Jan 1999 17:09:49 GMT, you wrote:

>
>i have two tables a,b with composite keys (c1,c2) and need to perform
>an outer join.
>
>select *
>from a,b
>where a.c1=b.c1(+) and
>a.c2=b.c2(+)
>
>gives wrong selections.

can you give an example. the query you have phrased above will:

for each row in A,
- find all of the rows in B or
- if no rows in B for a given C1,C2 exist, make up a NULL row in B

so, for example:

SQL> create table a ( c1 int, c2 int, primary key(c1,c2) ); Table created.

SQL> create table b ( c1 int, c2 int, foreign key(c1,c2) references a(c1,c2) ); Table created.

SQL> insert into a values ( 1, 1 );
1 row created.

SQL> insert into a values ( 2, 2 );
1 row created.

SQL> insert into b values ( 2, 2 );
1 row created.

SQL> select *
  2 from a,b
  3 where a.c1=b.c1(+)
  4 and a.c2=b.c2(+)
  5 /

C1 C2 C1 C2 ---------- ---------- ---------- ----------

         1          1
         2          2          2          2
         2          2          2          2

Thats the correct answer -- what were you looking for?

>
>(a.c1,a.c2)=(b.c1,b.c2) (+) is not accepted..
>
>a.c1||a.c2=b.c1||b.c2(+) is not accepted..
>
>ciao, jan

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 27 1999 - 11:35:53 CST