CROSS APPLY Operator

You need to combine 2 recordsets – a left-side and a right-side. You can use an inner join when both the left-side and the right-side are pre-defined. By pre-defined, I mean the the data already exists. You are just going to pull it directly as is. No calculations are required – no TOP, no GROUP BY etc

e.g. You have a table of Sales Traders and Trades. Each Trade record is associated to a SalesTrader via the column SalesTraderId

If I wanted to show all Trades for a SalesTrader, I would use an INNER JOIN because both the left-side ( SalesTrader ) and the right-side ( Trade ) are pre-defined.

The query-engine would get the left-side recordset ( Trade ), then the right-side recordset ( SalesTrader ) and then combine the matching rows – discarding rows where no match was found.

What I cannot do with an INNER JOIN is return results where the right-side is undefined
The right-side is undefined because in order to calculate it, we need the data from the left-side
e.g.
– Show me the last n trades for each SalesTrader
– Show me the total commission for each SalesTrader on a given day
– Show me the TOP 2 SalesTraders by Commission for a given date range

For this type of query, you can use the CROSS APPLY operator.
You can think of the CROSS APPLY as row-by-row inner join.

e.g.
Show me the last n trades for each SalesTrader
Using the CROSS APPLY, the query engine will generate the left-side recordset ( Sales Trader ) and then for every row in the left-side, it will return the last 2 trades. Lastly, the query engine will join the left-side to the right-side to display the last 2 trades for each SalesTrader. Because it is a CROSS APPLY, SalesTraders who have not placed a trade will not be displayed in the resultset.

I’m generating the right-side recordset using a table-valued function:

If I wanted the last 2 trades for each sales trader:

‹ SQL Server – User Defined Table Types

Delegate Basics Part 1 ›

Posted in Uncategorized