SQL: Work arounds for multi-column IN queries in T-SQL

We all use IN when writing queries:

SELECT ProductID, ProductName, Color, Size 
FROM dbo.Products 
WHERE Size IN ('370ml', '220ml');

A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:

Size        Color  370ml   Blue 370ml   Red 220ml   Blue

How do I find those when using IN?

Other databases do allow you to have pairs:

SELECT ProductID, ProductName, Color, Size
FROM dbo.Products 
WHERE (Size, Color) 
    IN (('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue'));

I wish T-SQL had that option but it doesn’t. It would be particularly useful when those values in the IN clause are coming from a sub-query.

Using CONCAT

One option is to use CONCAT to concatenate the strings. It takes a list of values, ignores NULL values, implicitly casts all values to strings, and concatenates the results.

SELECT ProductID, ProductName, Color, Size 
FROM dbo.Products 
WHERE CONCAT(Size, Color) 
IN 
(
    CONCAT('370ml', 'Blue'), 
    CONCAT('370ml', 'Red'), 
    CONCAT('220ml', 'Blue')
);

That’s the solution that looks closer to what you were trying to achieve, but it does more work than needed.

Using VALUES and a join

The other way is to just create a table expression using row constructors and to then join to it:

SELECT p.ProductID, p.ProductName, p.Color, p.Size 
FROM dbo.Products AS p 
INNER JOIN 
( 
    VALUES('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue') 
) AS c(Size, Color) 
ON c.Size = p.Size 
AND c.Color = p.Color;

Using EXISTS

Tiago Rente reminded me in the comments on LinkedIn that of course we could have used EXISTS rather than an INNER JOIN. I actually prefer the EXISTS as it keeps the filtering in a single predicate. It could look like this:

SELECT p.ProductID, p.ProductName, p.Color, p.Size 
FROM dbo.Products 
WHERE EXISTS (SELECT 1 FROM 
                 ( VALUES('370ml', 'Blue'), 
                         ('370ml', 'Red'), 
                         ('220ml', 'Blue') ) AS Selected(Size, Color) 
              WHERE Selected.Size = p.Size 
              AND Selected.Color = p.Color);

Using a CTE for Clarity

Anthony Duguid also mentioned in the LinkedIn comments that he’d like a CTE in there, to allow for a clearer name for the subquery. In fact, I think I’d like the combination of a CTE and EXISTS the best:

WITH SelectedProducts
AS
(
    SELECT Size, Color 
    FROM ( VALUES('370ml', 'Blue'), 
                 ('370ml', 'Red'), 
                 ('220ml', 'Blue') 
         ) AS sp(Size, Color)
)
SELECT p.ProductID, p.ProductName, p.Color, p.Size 
FROM dbo.Products 
WHERE EXISTS (SELECT 1 FROM SelectedProducts AS sp
                       WHERE sp.Size = p.Size 
                       AND sp.Color = p.Color);

2021-01-26