SQL Server Performance

'IN' vs 'ANY'

Discussion in 'Performance Tuning for DBAs' started by nanban, May 29, 2007.

  1. nanban New Member

    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

  2. MohammedU New Member

    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.
  3. nanban New Member

    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
  4. Adriaan New Member

    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.

Share This Page