Using CROSS APPLY In SQL SELECT Statements

In this article I  take you through the following:

• CROSS APPLY

• OUTER APPLY

• CROSS APPLY with User Defined Table Valued Functions

When Should We Use The CROSS APPLY Statement

The CROSS APPLY statement behaves in a similar fashion to a correlated subquery, but allows us to use ORDER BY statements within the subquery. This is very useful where we wish to extract the top record from a sub query to use in an outer sub query.

CROSS APPLY is also used when we wish to pass values into a User Defined Table Valued Function.

CROSS APPLY

The following example produces a list of orders along with the number of days between the order and the next order placed by the same customer.

CROSS APPLY enables us to join the order records (Orders AS o1) to the subquery (derived table named as ca), but we can also use an ORDER BY in the subquery to sort the order records in the subquery in ascending order of orderdate to enable us to identify the first order (TOP 1) after the current record’s orderdate.

OUTER APPLY

The OUTER APPLY statement behaves like an OUTER JOIN.

The following example will retain all orders from the outer query (Orders AS o1) even if there are no subsequent orders. The previous version will only display orders that have subsequent orders.

CROSS APPLY and Table Valued Functions

Table Valued Functions return a set of records as output. If we simply wish to view all records returned for a given input value we can call a user defined function as follows:

If, however, we wish to pass a value into the function for each record in another table we need to join the user defined function and the table. This requires a CROSS APPLY statement as we cannot use an INNER JOIN with an ON clause – the join value needs to be passed into the function.

See the next example.

To include all customer records, even those that do not have associated contact information (stores, for example) use an OUTER APPLY:

If you would like to know more about SQL Server SELECT Statements email us at info@ptr.co.uk with your questions, or why not take a look at our SQL Server Database Querying training courses:

SQL Server Database Querying

Advanced SQL Server Database Querying