Re: NOT IN is very inefficient

Steve Koterski wrote:
>
> On Thu, 24 Sep 1998 22:06:41 GMT, bjohnsto_usa_net_at_my-dejanews.com wrote:
>
> >SELECT my_field FROM my_table1
> >WHERE my_field NOT IN
> > (SELECT my_field FROM my_table2)
> >
>
> SELECT T1.my_field
> FROM my_table1 T1
> LEFT OUTER JOIN my_table2 T2
> ON (T1.my_field = T2.my_field)
> WHERE (T2.my_field IS NULL)
>

For Oracle that would be then:

SELECT T1.my_field
FROM my_table1 T1, my_table2 T2
WHERE T1.my_field = T2.my_field(+)
AND T2.rowid IS NULL

Yet another possibility (I've discovered that such a construction is sometimes faster when both tables are on different instances, i.e. via a database link; it is SLOWER when used against local tables):

SELECT my_field
FROM my_table1_at_database1
MINUS
SELECT my_field
FROM my_table2_at_database2

Marc Received on Fri Sep 25 1998 - 00:00:00 CDT