A few months ago, I was asked a question by a friend of mine who is also a Data Platform MVP. He was trying to express a query like this:
[code language="sql" gutter="false"]
SELECT si.StockItemID, si.StockItemName,
si.Size, si.LeadTimeDays
FROM Warehouse.StockItems AS si
WHERE (si.Size, si.LeadTimeDays)
IN (('M', 7), ('XXL', 7), ('XXL', 12));
[/code]
He was trying to select stock items where the combinations of Size and LeadTimeDays are in the list. Unfortunately, in SQL Server and T-SQL, an IN can only be used with a single column. We can't use a row constructor like (si.Size, si.LeadTimeDays) as a lookup into a list of constructed rows.
But it's not just IN where the problem exists. It's the same for this type of query (which also isn't allowed):
[code language="sql" gutter="false"]
SELECT si.StockItemID, si.StockItemName,
si.Size, si.LeadTimeDays
FROM Warehouse.StockItems AS si
WHERE (Size, LeadTimeDays) IN
(
SELECT Size, LeadTimeDays
FROM Sales.RequiredCombinations
);
[/code]
<h3>CROSS APPLY to the rescue</h3>
Now I've seen people come up with all sorts of messy solutions to this, but they often don't realize that it can be done by using CROSS APPLY. The normal use of CROSS APPLY is to call a table valued function (TVF) for every row in the left-hand table but it can do much more. Here's an example of that first query rewritten (in a way that does work):
[code language="sql" gutter="false"]
SELECT si.StockItemID, si.StockItemName,
si.Size, si.LeadTimeDays
FROM Warehouse.StockItems AS si
CROSS APPLY
(
SELECT Size, LeadTimeDays
FROM (VALUES ('M', 7), ('XXL', 7), ('XXL', 12))
AS rc(Size, LeadTimeDays)
) AS r
WHERE si.Size = r.Size
AND si.LeadTimeDays = r.LeadTimeDays;
[/code]
You can see the output in the main image above. Note that the correct combination of columns has been selected.
And similarly, the second query could also be rewritten like this:
[code language="sql" gutter="false"]
SELECT si.StockItemID, si.StockItemName,
si.Size, si.LeadTimeDays
FROM Warehouse.StockItems AS si
CROSS APPLY
(
SELECT Size, LeadTimeDays
FROM Sales.RequiredCombinations
) AS r
WHERE si.Size = r.Size
AND si.LeadTimeDays = r.LeadTimeDays;
[/code]
I hope that helps someone who's trying to do this.