CROSS APPLY and OUTER APPLY - Rishan Solutions

CROSS APPLY and OUTER APPLY in SQL Server: A Comprehensive Guide

In SQL Server, CROSS APPLY and OUTER APPLY are operators that are used to join a table to a table-valued function or a subquery. These operators are part of SQL Server’s powerful querying features, providing functionality similar to that of a join but with some critical differences. While both operators are used for similar purposes—namely joining rows from one table with rows from another—they behave differently, especially when dealing with cases where no matching data exists.

The purpose of this guide is to provide an in-depth understanding of the CROSS APPLY and OUTER APPLY operators, including their syntax, use cases, advantages, and key differences. This article will also explore practical examples, performance considerations, and the best practices for using CROSS APPLY and OUTER APPLY in SQL Server.

1. Introduction to APPLY Operators

Before diving into the specifics of CROSS APPLY and OUTER APPLY, it’s essential to understand what they do and why they exist.

1.1. What is an APPLY Operator?

The APPLY operator is used in SQL Server to join a table with a table-valued function or a derived table (e.g., a subquery) that returns multiple rows. This operator applies the function or subquery to each row of the outer table and returns the result set.

Unlike a regular JOIN, where both tables are required to have matching rows for a record to be included in the result set, the APPLY operator allows the outer query (usually the main table) to be joined to a derived table or function that can return a varying number of rows for each row of the outer table.

There are two variants of the APPLY operator:

CROSS APPLY: It returns only the rows from the outer table for which the APPLY function or subquery produces at least one result.
OUTER APPLY: It returns all rows from the outer table, even if the APPLY function or subquery produces no results (nulls are returned for rows where there is no match).

2. Understanding CROSS APPLY

2.1. What is CROSS APPLY?

The CROSS APPLY operator works similarly to an inner join. It returns only those rows from the outer table (the main table) that have at least one matching result from the table-valued function or subquery.

In other words, the CROSS APPLY operator applies the function or subquery to each row in the outer table and includes the outer table’s row in the result set only if the applied function returns at least one row.

Syntax of CROSS APPLY:

SELECT columns
FROM outer_table
CROSS APPLY table_valued_function_or_subquery;

Here, outer_table is the table on which the APPLY operator is used, and table_valued_function_or_subquery is the table-valued function or subquery applied to each row of outer_table.

Example of CROSS APPLY:

Let’s say we have an Employees table and a function GetEmployeeProjects that returns a list of projects an employee is working on. The following example demonstrates how CROSS APPLY can be used to retrieve a list of employees along with their projects.

-- Create a sample Employees table
CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(100));

-- Create a sample function to return employee projects
CREATE FUNCTION GetEmployeeProjects (@EmployeeID INT)
RETURNS TABLE
AS
RETURN (
    SELECT ProjectID, ProjectName
    FROM Projects
    WHERE EmployeeID = @EmployeeID
);

-- Use CROSS APPLY to join Employees with the results from GetEmployeeProjects
SELECT E.Name, P.ProjectName
FROM Employees E
CROSS APPLY GetEmployeeProjects(E.EmployeeID) P;

In this example:

GetEmployeeProjects(E.EmployeeID) is a table-valued function that returns the projects for a given employee.
CROSS APPLY ensures that only employees who are assigned at least one project will be included in the results.

2.2. Key Characteristics of CROSS APPLY:

Inner Join Behavior: CROSS APPLY only includes rows from the outer table where the applied function returns a result. If the function returns no rows for a given outer table row, that row is excluded from the result set.
Efficient for Non-Empty Results: When you expect the function to return results for most of the rows, CROSS APPLY is highly efficient and can be used to optimize complex queries.

3. Understanding OUTER APPLY

3.1. What is OUTER APPLY?

The OUTER APPLY operator is similar to CROSS APPLY, but with one key difference: it returns all rows from the outer table, even if the applied function or subquery returns no results. If no matching rows are returned by the function or subquery for a particular outer table row, OUTER APPLY will still include that row from the outer table, with NULL values for the columns from the applied function.

Syntax of OUTER APPLY:

SELECT columns
FROM outer_table
OUTER APPLY table_valued_function_or_subquery;

Here, the behavior is similar to CROSS APPLY, but with the guarantee that rows from the outer table are returned even if no results are produced by the function or subquery.

Example of OUTER APPLY:

Let’s consider the same Employees table and the GetEmployeeProjects function, but this time we want to include employees who do not have any projects. Using OUTER APPLY, we can retrieve all employees, even if they don’t have associated projects.

SELECT E.Name, P.ProjectName
FROM Employees E
OUTER APPLY GetEmployeeProjects(E.EmployeeID) P;

In this case:

Employees who do not have any projects will still be included in the results, but the ProjectName will be NULL for those employees.

3.2. Key Characteristics of OUTER APPLY:

Left Outer Join Behavior: OUTER APPLY behaves like a left outer join, where all rows from the outer table are returned, and if there are no matching rows from the function or subquery, NULL values are returned for the columns from the function or subquery.
Useful for Missing Data: OUTER APPLY is helpful when you want to return all rows from the outer table, regardless of whether the applied function or subquery produces any results.

4. Key Differences Between CROSS APPLY and OUTER APPLY

While CROSS APPLY and OUTER APPLY might seem similar, there are important differences that affect how they behave, especially when there are no matches for a given row from the outer table.

Feature	`CROSS APPLY`	`OUTER APPLY`
Matching Rows	Only includes rows where the applied function/subquery returns results.	Includes all rows from the outer table, even if the applied function/subquery returns no results.
Behavior	Behaves like an inner join.	Behaves like a left outer join.
Null Handling	Does not return rows for which the function/subquery returns no results.	Returns rows with `NULL` for columns of the function/subquery when no result is returned.
Use Case	Used when you only want results where the applied function/subquery returns data.	Used when you want all rows from the outer table, including those where the applied function/subquery returns no results.

5. Performance Considerations for APPLY

5.1. Performance of CROSS APPLY

CROSS APPLY can be more efficient when the applied function or subquery returns a small number of results, and the function is deterministic (i.e., the same input will always produce the same output).
It is often faster than a JOIN when working with table-valued functions because the function is applied to each row individually, and SQL Server can optimize these operations.

5.2. Performance of OUTER APPLY

OUTER APPLY can be less efficient than CROSS APPLY because it must include all rows from the outer table, even if no rows are returned by the applied function. This means that it may need to process and return a larger result set, especially when the outer table is large and the function produces few results.

5.3. Execution Plan Considerations

SQL Server generates execution plans for both CROSS APPLY and OUTER APPLY, and the optimizer treats them differently. CROSS APPLY is usually handled more efficiently because it behaves like an inner join, which SQL Server optimizes well.
OUTER APPLY, on the other hand, is treated like a left outer join, which can be more expensive in terms of both CPU and I/O, especially if the outer table contains many rows.

6. Use Cases for CROSS APPLY and OUTER APPLY

6.1. CROSS APPLY Use Cases

Filtering Nested Data: When you want to filter rows based on the results of a function or subquery.
Generating Complex Result Sets: When you need to generate a complex result set from a table-valued function and only want rows that have matching results.

6.2. OUTER APPLY Use Cases

Including Missing Data: When you want to include all rows from the outer table, even if the function or subquery returns no results.
Combining Data from Multiple Sources: When you need to return rows from the outer table alongside additional data from a subquery or table-valued function, even if that data is missing for some rows.

CROSS APPLY and OUTER APPLY are two powerful operators in SQL Server that allow you to join tables to table-valued functions or subqueries. While they both serve similar purposes, they differ in how they handle the inclusion of rows from the outer table based on whether the applied function or subquery produces matching results.

CROSS APPLY should be used when you only want to include rows from the outer table that have matching results from the applied function or subquery.
OUTER APPLY should be used when you want to ensure that all rows from the outer table are included, regardless of whether the function or subquery returns matching results.

Both operators provide flexibility and can significantly improve query performance when used correctly. However, they should be used judiciously, considering performance implications and the requirements of the query. By following best practices and carefully analyzing execution plans, you can take full advantage of CROSS APPLY and OUTER APPLY in SQL Server to write efficient and powerful queries.