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!
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.
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
- 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
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?
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.
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.