Re: SQL question

From: Wolfgang Breitling <john.doe_at_aol.com>
Date: Wed, 05 Nov 2003 01:44:40 GMT
Message-ID: <Xns9429BEADFACE3breitliwcentrexcccom@198.161.157.145>

Why not simply

select A.col1, A.col2 from tableA A
minus
select B.col1, B.col2 from tableB B

or

select A.col1, A.col2 from tableA A
where (A.col1, A.col2) not in (
  select /*+ hash_aj */ B.col1, B.col2 from tableB B   )

or

select A.col1, A.col2 from tableA A
where not exists (
  select /*+ hash_aj */ null
  from tableA A, tableB B
  where A.col1 = B.col1
    and A.col2 = b.col2
  )

Which of these performs better depends on the sizes of the tables involved and the number of matches vs mismatches.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in news:3fa8270c$0$3504$afc38c87_at_news.optusnet.com.au:

>
> "Alex" <alexsm_at_eudoramail.com> wrote in message
> news:29ff93bc.0311041325.bcc6111_at_posting.google.com...

>> Hi ALL
>>
>> I've two tables and am trying to apply an OUTER JOIN on multiple
>> fields. 
>>
>> Table A has some rows as:
>>
>> <Table A>
>> --- ---
>>  1   a
>>  1   b
>>  2   b
>>  3   a
>>  3   c
>>
>> Table B is missing some of the rows.
>>
>> <Table B>
>> --- ---
>>  1   a
>>  2   b
>>  3   c
>>
>>
>> Looking for only those rows Table B is missing:
>>
>> ----------
>> 1 b   NULL
>> 3 a   NULL
>>
>>
>> The following is not working:
>>
>> select t1.number, t1.letter, t2.number, t2.letter
>> from TableA t1, TableB t2
>> where t1.number = (+)t2.number
>> and    t1.letter = (+)t2.letter

>
>
> Daft column names don't help, but this is standard behaviour: an outer
> join shows all the rows matched with one table AND the rows that are
> unique to it. So if you want to see only the rows that aren't matched
> off, you have to get rid of the ones that are matched.
>
> So in new 9i language, this will work (with apologies for picking
> equally daft column names, but at least mine aren't reserved
> keywords):
>
> select * from A left outer join B on
> (A.col1 = B.col1 and A.col2 = B.col2)
> minus
> select * from A join B on
> (A.col1 = B.col1 and A.col2 = B.col2);
>
> Using the older-style syntax you have, you might try this:
>
> select * from A, B
> where (A.col1=B.col1(+) and A.col2=B.col2(+))
> minus
> select * from A,B
> where (A.col1=B.col1 and A.col2=B.col2)
>
> Works for me, at any rate, but the execution plan isn't pretty.
>
> Regards
> HJR
>
>
>

-- 
What lies behind us and what lies before us are small matters when 
compared to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP

Received on Tue Nov 04 2003 - 19:44:40 CST