Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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
0
royjayd
Asked:
royjayd
2 Solutions
 
Surendra NathCommented:
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
 
PortletPaulCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now