Home Select from table where a pair of columns marches one of any many pairs of values

Questions

Select from table where a pair of columns marches one of any many pairs of values

June 17, 2024

I need to find all rows in a table where a pair of columns matches any entry from a list of pairs of values. For instance, when looking for a single column, you would do

SELECT *
FROM Table t
WHERE t.column IN ('value1', 'value2', 'value3');

What I need is basically something like this:

SELECT *
FROM Table t
WHERE (t.column1, t.column2) IN
  (('value1', 1),
   ('value2', 2),
   ('value3', 3));

Note that I am not looking for this:

SELECT *
FROM Table t
WHERE 
   t.column1 IN ('value1', 'value2', 'value3')
   AND t.column2 IN (1,2,3);

As that would give me all rows where column1 and columns2 match their lists independently of each other, as opposed to matching specific pairs. The pair ('value1', 3) should not match my target query.

How can I accomplish this in SQL Server?

>Solution :

You can join a VALUES constructor

SELECT t.*
FROM Table t
JOIN (VALUES
   ('value1', 1),
   ('value2', 2),
   ('value3', 3)
) v(colum1, column2)
  ON v.column1 = t.column1
 AND v.column2 = t.column2;

You can also use a Table Value Parameter or a table variable or a temp table.