Multiple columns IN support

  • Hi All,

    Currently I am working on oracle to MS SQL server migration project.

    SQL select query in Oracle:

    Select ID, NAME in EMP_TBL where (ID, NAME) in (('1','foo1'), ('2','foo2')).

    What is the alternate solution in MS SQL Server 2008?

  • One way would be the need to compare each column individually like below.

    SELECT

    ID,

    Name

    FROM

    EMP_TBL

    WHERE

    ID IN (1,2)

    AND

    Name IN ('foo1','foo2')

  • Thanks very much for your reply.

  • Does the Oracle query check both values in a row? Which would be this:

    SELECT

    ID,

    Name

    FROM

    EMP_TBL

    WHERE

    (ID = 1 AND Name = 'foo1')

    OR

    (ID = 2 AND Name = 'foo2')

  • SELECT ID, NAME

    FROM EMP_TBL

    INTERSECT

    (SELECT '1','foo1'

    UNION ALL

    SELECT '2','foo2')

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Any performance issues if there are more values in IN clause?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply