LuisLlamas Logo
operador-outer-apply-cross-apply

The CROSS APPLY and OUTER APPLY Operators

  • 4 min
  • Advanced

The APPLY operator (with its CROSS and OUTER variants) allows you to invoke a table-valued function (TVF) or a dynamic subquery for each row returned by a main table (the left-hand table).

If you’ve just mastered JOINS, you’ll feel invincible being able to connect any entity. But sooner or later you’ll hit a technical wall: in a standard JOIN, the right-hand side is “static”.

Imagine you have a Table-Valued Function (TVF) that accepts a parameter f(CustomerID) and returns that customer’s orders. You try to do this:

-- ⛔ THIS WILL ERROR
SELECT CustomerName, F.OrderID
FROM Customers
INNER JOIN dbo.f_GetOrders(Customers.CustomerID) F ON ...
Copied!

SQL Server will complain. Why? Because in a standard JOIN, the right-hand side is “static”. It cannot see or use the values from the current row on the left (C.CustomerID) to compute itself.

To solve this (and do much more interesting things), Microsoft introduced the APPLY operator.

It’s not a tool you might use every day, but the day you need it, it will save your bacon.

What is APPLY?

Think of APPLY as an optimized FOREACH loop for SQL.

Takes a row from the left-hand table (Table A).

“Injects” the values from that row into the expression on the right (Table B, Function, or Subquery).

Calculates the result for that specific row.

Joins the results and moves to the next row.

This allows what we call Correlation: the query on the right depends on the row from the left.

The Two Flavors: CROSS vs OUTER

The distinction is identical to the one between INNER JOIN and LEFT JOIN.

Equivalent to an INNER JOIN.

  • Executes the right-hand logic for each row on the left.
  • If the right-hand side returns no results, the left-hand row disappears.

Equivalent to a LEFT JOIN.

  • Executes the right-hand logic.
  • If the right-hand side returns no results, the left-hand row is kept and the right-hand columns are filled with NULL.

Joining with Table-Valued Functions (TVF)

This was the original reason for creating this operator. If you have a function that requires parameters from the main table, you must use APPLY.

SELECT
    C.Name,
    P.Product,
    P.Total
FROM Customers C
OUTER APPLY dbo.ufn_GetLast3Orders(C.CustomerID) P;
Copied!

Here, for each customer, SQL executes the function passing its ID.

  • If we used CROSS APPLY: Customers without orders would not appear in the report.
  • By using OUTER APPLY: Customers without orders appear with NULL in Product and Total.

The “Top N” per Group

This is a very useful case for APPLY, even if you don’t use functions. Imagine you want to get the 2 most expensive products for EACH category.

With JOIN and GROUP BY it’s difficult (you only get the maximum, not the “top 2” with their names). With ROW_NUMBER() it’s possible but verbose. With APPLY it’s intuitive:

SELECT
    Cat.CategoryName,
    Prod.ProductName,
    Prod.Price
FROM Categories Cat

OUTER APPLY (
    -- This subquery executes FOR EACH Category
    SELECT TOP 2 *
    FROM Products P
    WHERE P.CategoryID = Cat.CategoryID -- This is the key
    ORDER BY P.Price DESC
) Prod;
Copied!

What’s happening here?

  1. SQL takes the “Electronics” category.
  2. Enters the subquery, finds products ONLY from “Electronics”, orders them, and takes the top 2.
  3. Attaches them to the result.
  4. Moves to the “Clothing” category and repeats.

Here we use OUTER APPLY for safety: if there’s an empty category (no products), we want to see it in the list anyway (with NULLs), instead of having it disappear.

Splitting Columns (Split)

If you have a column with comma-separated data (something you should avoid, but it happens), APPLY together with the STRING_SPLIT function is the solution to normalize that data on the fly.

-- Table: Articles (ID, Title, Tags)
-- Row: 1, 'SQL News', 'sql,database,microsoft'

SELECT
    A.Title,
    S.value AS Tag
FROM Articles A
CROSS APPLY STRING_SPLIT(A.Tags, ',');
Copied!

This will multiply the article row as many times as it has tags.

On this page