Introduction

In this article, we are going to see how the SQL CROSS APPLY works and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets.

CROSS APPLY is basically equivalent to the LATERAL JOIN, and it’s been supported by SQL Server since version 2005 and Oracle since version 12c.

Database table

Let’s assume we have the following blog database table storing the blogs hosted by our platform:

And, we have two blogs currently hosted:

| id | created_on | title                | url                      |
|----|------------|----------------------|--------------------------|
| 1  | 2013-09-30 | Vlad Mihalcea's Blog | https://vladmihalcea.com |
| 2  | 2017-01-22 | Hypersistence        | https://hypersistence.io |

Getting the report without using the SQL CROSS APPLY

We want to generate a report that extracts the following data from our blog table:

• the blog id
• the blog age in years
• the next blog anniversary
• the number of days remaining until the next anniversary.

The blog age needs to be calculated by subtracting the blog creation date from the current date.

The date of the next blog anniversary can be calculated by incrementing the age in years and adding it to the blog creation date.

The number of days until the next anniversary can be calculated by extracting the number of days from the interval given by the next blog anniversary and the current date.

For SQL Server, we can use the following query to build the required report:

SELECT
  b.id as blog_id,
  FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457) AS age_in_years,
  DATEADD(
    year,
    ((FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457)) + 1),
    created_on
  ) AS next_anniversary,
  DATEDIFF(
    day,
    GETDATE(),
    DATEADD(
      year, 
      ((FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457)) + 1), 
      created_on
    )
  ) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id

And, we are going to get the expected result:

| blog_id | age_in_years | next_anniversary | days_to_next_anniversary |
|---------|--------------|------------------|--------------------------|
| 1       | 7            | 2021-09-30       | 23                       |
| 2       | 4            | 2022-01-22       | 137                      |

However, as you can see, the age_in_years has to be calculated three times. And, that’s exactly where CROSS APPLY can help us.

Getting the report using the SQL CROSS APPLY

CROSS APPLY allows us to reuse the age_in_years value and just pass it further when calculating the next_anniversary and days_to_next_anniversary values.

For instance, the previous SQL Server query can be rewritten like this:

SELECT
  b.id as blog_id,
  age_in_years,
  DATEADD(
    year, 
	(age_in_years + 1), 
	created_on
  ) AS next_anniversary,
  DATEDIFF(
    day, 
	GETDATE(), 
	DATEADD(year, (age_in_years + 1), created_on)
  ) AS days_to_next_anniversary
FROM blog b
CROSS APPLY (
    SELECT
    FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457) AS age_in_years
) AS t
ORDER BY blog_id

Much better, right?

The age_in_years is calculated for every record of the blog table. CROSS APPLY works like a correlated subquery, but, unlike a correlated subquery, the subquery records are also JOIN-ed with the primary table, and, for this reason, we can reference the columns produced by the CROSS APPLY subquery.

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

CROSS APPLY is a very useful feature when working with SQL Server. You can use it with OPENJSON to map a JSON array to a relational database table that you can further transform using all the available SQL features.

Unlike joining directly with a Derived Table, CROSS APPLY is evaluated for every record in the primary table, and not just once.

Insert details about how the information is going to be processed

DOWNLOAD NOW