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.
2021-01-26