Understanding the distinction between Cross Apply and Outer Apply is essential for writing efficient set-based queries in T-SQL. Both operators allow you to invoke a table-valued function for each row from an outer table, but they handle rows that yield no results differently. This subtle difference dramatically impacts the result set and performance of your queries, particularly when dealing with hierarchical data or top-n-per-group problems.
Core Mechanics: How They Function
Cross Apply operates like an INNER JOIN, passing each row from the left table expression to the right-side table-valued function. If the function returns at least one row, that data is included in the final output; if it returns nothing, the parent row is excluded entirely. Outer Apply, on the other hand, functions like a LEFT JOIN, preserving every row from the left side regardless of the function's output. When the right side returns no data, the result set still includes the left row, filled with NULLs for the columns of the table-valued function.
Syntax Comparison
The syntax for both operators is nearly identical, making the choice between them a matter of intent. You place the operator immediately after the FROM clause, followed by the table-valued function or derived table you wish to invoke. The correlation name in the ON TRUE clause (or WHERE 1=1) links the right-side execution to the current row of the left table. This correlation is what enables row-by-row processing without the performance penalty of a cursor.
Practical Use Cases and Data Preservation
When your goal is to filter the driving table based on the existence of related data, Cross Apply is the ideal tool. For instance, if you need to find customers who have placed at least one order, joining a Customer table to an Orders function via Cross Apply will naturally exclude those with zero transactions. Conversely, when you must retain all primary records while optionally enriching them, Outer Apply shines. Generating a report of all employees alongside their most recent project—where some employees might be unassigned—is a perfect scenario for Outer Apply, ensuring no one is left off the list due to missing data.
Handling Hierarchical Data
One of the most powerful applications of these operators is navigating hierarchical structures, such as organizational charts or bill-of-materials. By using a recursive Common Table Expression (CTE) in the right-side input, you can traverse parent-child relationships efficiently. Cross Apply ensures you only see nodes that have descendants, while Outer Apply allows you to display leaf nodes that have no children as standalone entries. This flexibility is critical when the business logic requires visibility into the existence or absence of child rows.
Performance Considerations and Optimization
Performance between Cross Apply and Outer Apply is generally similar because the query optimizer treats them with the same cardinality estimation model. The key to optimization lies in the set-based definition of the right-side input. If you pass a multi-statement table-valued function that processes thousands of rows per call, the operator will execute that function iteratively, leading to a nested loops join behavior. To maximize efficiency, always prefer inline table-valued functions, which the optimizer can flatten and integrate into a single execution plan, rather than relying on procedural code.
Top-N Per Group Patterns
Arguably the most famous use of Cross Apply is solving the "top-n per group" problem. Without these operators, you might resort to complex window functions or inefficient cursors. By applying a TOP clause within the right-side input, ordered by your desired criteria, you can fetch the single most relevant row for each left-side entry. Outer Apply modifies this behavior slightly by including the group even if the TOP clause returns zero rows, which is useful for scenarios where a default value is preferred over a complete absence of data.
