Opimize IN clause | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Opimize IN clause

I have a query like so:<br /><br />select homeAddress from userHome where zipCode IN (Select ZipCode From LotsOfZips)<br /><br />The subquery of ZipCode(s) From LotsOfZips returns 1800 zipcodes.[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]<br /><br />this take a long time to query.<br /><br />any suggestions on optimizing this?<br /><br />thanks!<br /><br />-kevin
Do you have an index on column zipCode in both tables? If not add them. With indexed on this column on both table you should see 2 index seeks.
Otherwise check the execution plan and tell us what you see there.
Make sure statistics is updated on tables. Bambola.
Follow Bambola’s Solution… Won’t this work??
Select homeadress from userhome
Join LotsofZips
on homeadress.zipcode = LotsofZips.ZipCode Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Gaurav, I believe that does the job – thanks… I also followed Bambola’s advice. much better now ya’ll. muchas gracias.