replace IN with EXIST

hi experts

Can theperformance of this sql be improved by using a EXIST clause instead of  IN ?

DELETE FROM CUSTOMER  WHERE ID IN (SELECT ID FROM CUSTOMER WHERE  System='MAC' AND BusinessDate='2013-05-06' )

thanks
royjaydAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
yes, it can be, if you are looking for a exists solution it can be written as below

DELETE FROM CUSTOMER  C
WHERE EXISTS (SELECT 1 FROM CUSTOMER C1 WHERE  C1.System='MAC' AND C1.BusinessDate='2013-05-06' and C.id = C1.ID)

Open in new window

0
 
PortletPaulConnect With a Mentor freelancerCommented:
EXISTS will generally outperform IN() - and can be substantially better.
however the difference is marginal if the numbers involved are small

my personal "rule of thumb" regarding IN() is that you don't use it if the number of items within the brackets is an unknown quantity. i.e.

select * from whatever where fieldx IN (1,2,3) -- known quantity = OK
select * from whatever where fieldx IN (select X from Y) -- unknown quantity = Not OK

IN() is an abbreviated form of a list of OR operators and can be dreadfully slow if the number of items being evaluated is very large.

ps: Neo_jarvis and I also seem to agree on using "select 1 ...".  The inner subquery only needs to return a flag when using EXISTS, and the constant 1 does this nicely.

pps: for me, "rules of thumb" are not absolute, just guides.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.