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?
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.