What is the usage of outer apply in SQL?

In SQL, OUTER APPLY is a type of join operation that applies a subquery within a query, returning the results of the subquery as part of the outer query. Similar to INNER JOIN and CROSS APPLY, but distinct in that OUTER APPLY returns all rows of the outer query, even if the subquery does not return any results.

The syntax for OUTER APPLY is as follows:

SELECT [列名]
FROM [表名]
OUTER APPLY (子查询) AS [别名]

In a subquery, you can use columns from the outer query as input and then return the results of the subquery within the outer query. This mechanism is very useful, especially when calculations or filters need to be done based on each row of the outer query.

Here is an example of using OUTER APPLY:

Suppose we have two tables: Customers and Orders. We want to query the number of orders for each customer, even if the customer does not have any orders. We can achieve this using OUTER APPLY.

SELECT c.CustomerName, o.OrderCount
FROM Customers c
OUTER APPLY (
    SELECT COUNT(*) AS OrderCount
    FROM Orders
    WHERE CustomerID = c.CustomerID
) o

In this example, the OUTER APPLY takes the subquery as part of the outer query and returns the number of orders for each customer. It will still return 0 as the order count even if a customer does not have any orders.

Summary: OUTER APPLY is used to apply a subquery within a query and return the results of the subquery as part of the outer query. It processes each row of the outer query and returns the results of the subquery, even if the subquery does not return any results.