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

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

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

SELECT ProductID, ProductName, Color, Size

FROM dbo.Products

WHERE CONCAT(Size, Color)

(

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;

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

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

WITH SelectedProducts

(

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

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

Using CONCAT

Using VALUES and a join

Using EXISTS

Using a CTE for Clarity

One thought on “SQL: Work arounds for multi-column IN queries in T-SQL”

Leave a Reply Cancel reply