Re: SQL question

"Wolfgang Breitling" <john.doe_at_aol.com> wrote in message news:Xns9429BEADFACE3breitliwcentrexcccom_at_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.

Agreed. All I was trying to do was use the OP's original query and show where it was lacking. I wasn't trying to suggest that mine was a particularly good way of doing it. The reference to the appalling execution plan was intended to be the clue!

I think the fundamental issue here was the assumption (or desire) that an outer join would show only the non-matched rows.

Regards
HJR Received on Tue Nov 04 2003 - 19:58:10 CST