T-SQL 101: 115 Repetitively selecting with CROSS APPLY, OUTER APPLY

We saw in previous post that we had a function called GetRecentOrders that gets the most recent orders for a particular cinema.

But what if we want to do that for all the cinemas?

If I’d like the two most recent orders for every cinema, that becomes messy. I can’t just do that with a join, like an inner join or outer join. The function provided is fine. We can use that, but we want to call it for every row in the table.

And that’s what CROSS APPLY does. It says to call the table-valued function (TVF) on the right, for every row in the row set on the left. And then return the results from each execution as a single set of rows.

OUTER APPLY

One of the challenges with CROSS APPLY is that the left-hand row set is only returned if it matches anything in the TVF. In this case, if a cinema has no recent orders, then no rows would be returned for the cinema.

Similar to the way that outer joins work, we have OUTER APPLY. It’s the same as CROSS APPLY but it will return at least one row for every row in the left-hand table. Any non-matching columns from the TVF will be returned as NULL.

If I use it in the above query, then every cinema would be returned, even if they had no recent orders.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-14