SQL: Using CROSS APPLY to replace multi-column predicate look-ups in T-SQL

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:

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

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

SELECT si.StockItemID, si.StockItemName, si.Size, si.LeadTimeDays FROM Warehouse.StockItems AS si WHERE (Size, LeadTimeDays) IN ( SELECT Size, LeadTimeDays FROM Sales.RequiredCombinations );

CROSS APPLY to the rescue

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

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;

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:

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;

I hope that helps someone who’s trying to do this.

2019-06-13