Select where all columns in list item-CodePudding

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.

CodePudding user response：

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)
       )

CodePudding user response：

SQL Server does not support this tuple syntax, so you'll have to use the more verbose version:

SELECT *
FROM dbo.TableA
WHERE (ColA = 1 AND ColB = 2 AND ColC = 3) OR
      (ColA = 1 AND ColB = 1 AND ColC = 1);

For many tuples, you really should have the values in some other table. Then, you could simply join to that table to get the filtering effect you want:

SELECT *
FROM dbo.TableA a
INNER JOIN FilterTable b
    ON b.ColA = a.ColA AND
       b.ColB = a.ColB AND
       b.ColC = a.ColC;