SQL CROSS APPLY – A Beginner’s Guide
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
