SQL Server OUTER APPLY

 OUTER APPLY is a powerful operator used to join each row from an outer table to the results of a table-valued function or subquery. It behaves like a LEFT JOIN, but works with correlated subqueries that return multiple columns.

OUTER APPLY:

• Evaluates the right-hand subquery for each row of the left table.

• Returns all rows from the left table.

• Returns matching rows from the APPLYed subquery — or NULLs when the subquery returns nothing.

It’s similar to:

Operator                      BehaviorCROSS APPLY         Like INNER JOINOUTER APPLY         Like LEFT JOIN

E.g

SELECT 

    c.CustomerID,

    c.Name,

    o.OrderID,

    o.OrderDate

FROM Customers c

OUTER APPLY (

    SELECT TOP(1) *

    FROM Orders o

    WHERE o.CustomerID = c.CustomerID

    ORDER BY o.OrderDate DESC

) o;

Cheers

Samitha