Re: NOT USING A NOT IN SQL

MINUS can be used in your particular example. In general, as Alan Mills said, it isn't the 'NOT' you should try to avoid, its the 'IN'. You can usually avoid 'IN' and 'NOT IN' by using 'EXISTS' and 'NOT EXISTS'.

For example,

select * from table1 where c1 in (select c2 from table2)

becomes :

select * from table1 t1 where exists
(select 'x' from table2 t2
 where t2.c2 = t1.c1)

This is often more efficient, as long as the join columns c1 and c2 are indexed, because fewer rows need to be checked in the subquery, as it can stop as soon as it finds a match.

You should also generally avoid NOT IN in favour of NOT EXISTS but for a different reason. You can change :

select * from table1 where c1 not in (select c2 from table2)

to:

select * from table1 t1 where not exists (select 'x' from table2 t2
 where t2.c2 = t1.c1)

but in this case, the efficiency is unlikely to improve much, because every row has to be checked for a non-match each time. The reason to avoid NOT IN here is that it can give the wrong answer if either of t1.c1 or t2.c2 can be null (no problem if they are both NOT NULL columns). I can't remember the exact details of why this is the case, but I've been caught out by it in the past !

D S Tharpe wrote in message <35D845DE.41C67EA6_at_ornl.gov>...
>What are some equivalent ways of SQL coding to avoid a 'not'
>in the following sql clause. I'm vaguely familiar with
>'exists' and 'minus'. The rule I've heard is never use a not -
>it takes much longer to return a result. Does anyone have
>experience with not using a 'not'?
>
>select c1 from table where c1 not in(select c2 from another_table);
>
>(c1 and c2 would have similar values)
>
>Thank you in advance for any advice,
>
>D. Tharpe
Received on Wed Aug 19 1998 - 07:14:26 CDT