Re: NOT USING A NOT IN SQL

I think the answer will be very much dependent on the specific SQL statement you wish to write (or re-write). You could arguably use an outer join or a MINUS or even a NOT EXISTS to rewrite your example.

The way you've phrased your question though, I'm not sure it's the NOT that takes the time but the use of IN against EXISTS whcih is the true comparison. In general I would opt for EXISTS against IN as my first attempt at coding. Using IN will retreive the entire set to compare against but using EXISTS will retrieve only as many rows as needed to answer the question (i.e. one row, usually) so it is quicker for that reason.

At least this is how I understand it. Comments anyone?

--
Alan D. Mills

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);
>
Received on Tue Aug 18 1998 - 06:11:48 CDT