PTR logo

Tech Tips

SQL CROSS APPLY Statement

The SQL APPLY statement is extremely useful for tackling hierarchical and nested data challenges, such as picking up a previous or next record to calculate a lag or gap between records.

Motion graphic.
SQL CROSS APPLY Statement

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

The examples in this blog are Microsoft SQL CROSS APPLY examples and will work on SQL Server, Azure SQL, Synapse and Fabric SQL.

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.

Content image

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.

Content image

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.

Content image

Content image

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:

Content image
Content image

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.

Content image
Content image

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

Content image

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

Share This Post

MD

Mandy Doward

Managing Director

PTR’s owner and Managing Director is a Microsoft MCSE certified Business Intelligence (BI) Consultant, with over 30 years of experience working with data analytics and BI.

Latest Articles

Frequently Asked Questions

Couldn’t find the answer you were looking for? Feel free to reach out to us! Our team of experts is here to help.

Contact Us