Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Difficult SQL Query
nomail_at_nowhere.fr wrote:
>
> select attribute1 from Table1
> minus
> select attribute2 from Table2;
>
> or
>
> select attribute1 from table1
> where
> attribute1 not in
> (
> select attribute2 from Table2
> );
>
> or
>
> select attribute1 from table1 T1
> where
> not exists
> (
> select 1 from Table2
> where
> attribute2= T1.attribute1
> );
My experience is that of the two queries using sub-queries, performance is far superior using the second approach:
select attribute1 from table1 T1
where
not exists
(
select 1 from Table2
where
attribute2= T1.attribute1
);
Avoid the "not in" clause whenever possible. -- Carl Received on Tue Sep 23 1997 - 00:00:00 CDT