SQL Server Performance Forum – Threads Archive

‘IN’ vs ‘ANY’

Which of the below queries will have better performance? 1) SELECT ename
FROM emp
WHERE deptno IN (10, 20, 30) 2) SELECT ename
FROM emp
WHERE deptno = ANY (10, 20, 30)
Regards
nanban

Query optimizer uses OR clause interanally when you use IN clause…
ANY used with sub-query, as your code it is better to use IN… select * from [Order Details]
where productid in (11, 42, 72)
go
select * from [Order Details]
where productid = ANY (select productid from [Order Details]
where productid in (11, 42, 72)) MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Mohammed,
Thanks for your reply. So, is it recommendable to use ‘ANY’ with sub query, in place of IN?
Will it improve the performance? Thanks and Regards
Nanban

If you have a subquery like this … WHERE colX = (SELECT colX FROM othertable WHERE colY = 12345) … and there are more than one rows returned by the subquery, you will get the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." One way of avoiding this would be to insert ANY between the = operator and the subquery. Note that this is the same as replacing the = operator with the IN keyword. You don’t see the ANY, SOME and ALL keywords used much.

]]>