Select where all columns in list item

April 11, 2022

How do I do the equivalent of the following in SQL Server?

SELECT * FROM dbo.TableA WHERE (ColA, ColB, ColC) IN ((1, 2, 3), (1, 1, 1));

I only want to match rows where (ColA = 1 AND Col2 = 2 AND Col3 = 3) OR (ColA = 1 AND Col2 = 1 AND Col3 = 1). There can be an arbitrary number of match conditions.

The only solution I have been able to find is the above, but doubt it would scale to 1000s of dynamic values.

>Solution :

Put those value in temp table or use Table Value Constructor

Use INTERSECT Set Operator

SELECT * 
FROM   dbo.TableA 
WHERE  EXISTS
       (
           SELECT ColA, ColB, ColC
           INTERSECT
           SELECT ColA, ColB, ColC
           FROM
           (
               VALUES
               (1, 2, 3),
               (1, 1, 1)
           ) v (ColA, ColB, ColC)
       )