CROSS APPLY is used to join a table with a table-valued function or a derived table.

It works like an INNER JOIN, meaning it only returns rows where the function or derived table returns results for the current row.

Useful for calling functions per row or joining dynamic datasets.

Basic syntax:

SELECT columns
FROM table1
CROSS APPLY table_valued_function_or_subquery AS alias;
  • table_valued_function_or_subquery is evaluated for each row of the left table.
  • Only returns rows where the right-side query/function produces results.

Step 1: Example tables

Employees Table

EmployeeID Name DepartmentID
1 John 101
2 Jane 102
3 Mike 101

Salaries Table

EmployeeID Salary
1 5000
1 5200
2 4500
3 6000

Step 2: Using CROSS APPLY with a subquery

Suppose we want the highest salary for each employee:

SELECT e.Name, s.MaxSalary
FROM Employees AS e
CROSS APPLY (
SELECT MAX(Salary) AS MaxSalary
FROM Salaries
WHERE Salaries.EmployeeID = e.EmployeeID
) AS s;

Result:

Name MaxSalary
John 5200
Jane 4500
Mike 6000

Explanation:

  • For each row in Employees (e), the subquery calculates the MAX salary from Salaries for that employee.
  • Only rows with matching salaries are returned (like INNER JOIN behavior).

Step 3: Difference between CROSS APPLY and OUTER APPLY

  • CROSS APPLY → only includes rows where the right-side query returns results.
  • OUTER APPLY → includes all rows from the left table, even if the right-side query returns NULL.

Example with OUTER APPLY:

SELECT e.Name, s.MaxSalary
FROM Employees AS e
OUTER APPLY (
SELECT MAX(Salary) AS MaxSalary
FROM Salaries
WHERE Salaries.EmployeeID = e.EmployeeID
) AS s;
  • If an employee had no salary records, MaxSalary would be NULL.
  • CROSS APPLY would exclude that employee.

Step 4: Key Notes

  1. CROSS APPLY is similar to INNER JOIN but for table-valued functions or subqueries.
  2. OUTER APPLY is similar to LEFT JOIN.
  3. Useful for calling functions per row or joining dynamic/different datasets per row.
  4. Cannot be replaced by a regular JOIN if the right-side is a correlated subquery or function.

Categorized in:

SQL Server,