Re: Trying to avoid using the IN Clause in SQL statment

Look for Replacing the IN clause with WHERE EXISTS it is much better. There is a difference in the way IN and EXISTS are processed. Generally EXISITS is faster. Read this "
The two ( IN and EXISTS) are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then 
joined to the original table -- typically.


As opposed to 

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

It always results in a full scan of T1 whereas the first query can make use of 
an index on T1(x).


So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
    ( select y from T2 )

is "huge" and takes a long time.  But the table T1 is relatively small and 
executing ( select null from t2 where y = x.x ) is very very fast (nice index on 
t2(y)).  Then the exists will be faster as the time to full scan T1 and do the 
index probe into T2 could be less then the time to simply full scan T2 to build 
the subquery we need to distinct on.


Lets say the result of the subquery is small -- then IN is typicaly more 
appropriate.


If both the subquery and the outer table are huge -- either might work as well 
as the other -- depends on the indexes and other factors."
Hope this was of help.
 [email protected] wrote:I have an ASP page that allows a user to submit a text file that is 
composed of one column of say... inventory stock numbers. I grab the file 
and retrieve the numbers. I then want to query several tables in an Oracle 
8.1.7 database for information for these values. I currently have designed 
the SQL like this.

"SELECT sometable.something, othertable.otherthing FROM sometable, 
othertable WHERE stockNumber IN ('VAL1','VAL2'.....)"

I realize this is inefficient for a large qty of "VALs" so I have limited 
the size of the file the user can submit however this is not a long-term 
solution. 

I am looking for some advice on maybe creating a temporary table and 
inserting the values into the table where then I can run queries against 
it efficiently. I know I could create table and "INSERT INTO .... VALUES
( ... )”, but is there an even better approach. Suggestions would be 
greatly appreciated.



---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].