SQL Tip #1: APPLY and why you should be using it

Today in our first SQL tips article we will cover the APPLY operator – SQL Server offers various methods to bring together data from multiple tables, the most common being subqueries, traditional joins, and the apply operator. While each approach has its own merits, the apply method stands out for its versatility and clean look.

If you don’t use outer apply/cross apply right now in your queries you’re really missing out and will hopefully add it to your SQL repertoire after reading this.

Two Flavors of the APPLY Operator

OUTER APPLY: Functionally similar to a LEFT JOIN, returns data for any rows that match the base table

CROSS APPLY: Functionally similar to a INNER JOIN, returns data for only rows that exist in both tables

In this article, we’ll discuss why using APPLY is such a great tool and why it is often a better tool than a JOIN or Subquery

What is Apply and how does it work?

To keep it simple, Apply is a way of performing a calculation for every row of a given table, in row-by-row style execution. You can consider it a more versatile version of a join, that allows you to do all your joining and filtering in one place and on a row by row precision level. Its a fast, clean way of creating aggregates without long subqueries or crazy joins.

Left Join Vs Apply Example

Lets start with an example comparing a Join to Apply, which of these looks cleaner? (Both return the exact same result of getting customers and their largest order)

Anatomy of APPLY

With APPLY, the “TOP 1” does the aggregation work, rather than the long windowed function inside the join

Why you should be using APPLY

Improved Readability: Subqueries can often lead to deeply nested code, making it difficult to read and maintain. Outer apply removes this nesting and centralizes the filters and join conditions

Handling Complex logic: In some cases, you may need to extract data from multiple tables with varying relationships. Outer Apply can simplify this process by allowing you define your logic all in one place, and aggregate without ugly Row_Number() usage or joining to the same table twice after aggregating

Working with Table-Valued Functions: OUTER APPLY shines when working with table-valued functions, as it allows you to pass columns from the primary table as parameters to the function, providing a level of flexibility that traditional joins cannot achieve. Plus, this just looks clean:

Enhanced Performance: This one can depend on a lot of factors, but often APPLY can be faster than a join or subquery. Subqueries especially can cause performance issues, same goes for complex joins or aggregates.

Row-by-Row Execution: Unlike traditional joins, OUTER APPLY executes the defined table expression for each row in the primary table. This row-by-row execution can be beneficial when dealing with complex calculations or data transformations, which might be challenging to accomplish with traditional joins.

For even more details, check out the full Microsoft documentation

Wrapping up

While apply isn’t a one-size-fits-all solution, it provides significant advantages over subqueries and traditional joins in many situations. Give it a shot the next time you need go to join to an aggregate or want to avoid a long subquery. Both Outer Apply and Cross Apply make regular appearances in my SQL and have become some of my favorite tools in the proverbial SQL belt. As a bonus, its a great way to impress your coworkers- but be warned they will almost certainly start using it as well.