SQL Tips: CTE and Outer Apply (part 2 of 2)

Welcome to learning about Outer Apply in this newsletter and the reason why.

Hello everyone, it’s Brien! Your fellow data professional still figuring things out as I make my way through year three in the field. I hope your week is going well.

Today’s newsletter is a continuation of Part 3 of my roadmap to becoming a data analyst. I promised that I would go over CTEs and OUTER APPLY, two SQL concepts that can really level up the way you think about data once you understand how they work.

Photo by Mohammad Rahmani on Unsplash

What is an OUTER APPLY?

---

If you looked at the last newsletter, with CTE, you have make a virtual table first then you had to use a join to the main query statement. With an outer apply, you can just join base on the row data. And not all at once. There is some example code:

SELECT 
    c.CustomerName, 
    o.OrderID, 
    o.TotalAmount
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 OrderID, TotalAmount 
    FROM Orders o 
    WHERE o.CustomerID = c.CustomerID -- Correlated reference
    ORDER BY o.TotalAmount DESC
) o;

When would you use an OUTER APPLY?

---

One of the most practical use cases I’ve found for OUTER APPLY is when you need to pull one specific row from a related table ; usually the “latest,” “current,” or “most active” record.

For example, imagine a status change log where each entity has multiple statuses over time. If I want the current status, I can’t rely on a normal JOIN because JOINs don’t let me order the related rows or filter them in a way that guarantees I only get the top record.

With OUTER APPLY, I can run a subquery per row of the main table, apply an ORDER BY inside that subquery, filter for endDate IS NULL, and return exactly one row — the active status.

In other words:
JOIN brings back all related rows. OUTER APPLY lets you pick the best related row.

OUTER APPLY (
    SELECT TOP 1 *
    FROM StatusLog s
    WHERE s.entity_id = e.id
      AND s.endDate IS NULL
    ORDER BY s.startDate DESC
) AS CurrentStatus

What use cases have you used an OUTER APPLY for?

Please put your answer in the comments