Explain a SQL Function

Explain the function of CROSS APPLY in the code below:

SELECT customer.CustomerId, customer.CustomerName, ord.*
FROM customers customer
CROSS APPLY
(
    SELECT SUM(order.quantity) as TotalQuantity
    FROM orders order
    WHERE order.CustomerId = customer.CustomerId
) ord;

The CROSS APPLY function in this SQL code acts as a correlated subquery that applies the results of the subquery to each row in the customers table. Here's a breakdown:

1. Outer Query: The main query selects CustomerId and CustomerName from the customers table, aliased as customer.

2. CROSS APPLY: This keyword introduces the correlated subquery. It instructs the database to execute the subquery for each row in the customers table.

3. Subquery: The subquery calculates the TotalQuantity for each customer by:

4. Selecting the sum of quantity from the orders table, aliased as order.

5. Filtering the orders table based on the CustomerId from the current row in the customers table.

6. Result: The CROSS APPLY returns a single row for each customer, containing the CustomerId, CustomerName, and the calculated TotalQuantity.

In essence, CROSS APPLY allows you to dynamically generate a new column (TotalQuantity) for each customer based on the results of the subquery, effectively joining the customers table with the aggregated order data.

Example:

Let's say you have the following data:

Customers:

CustomerId	CustomerName
1	John Doe
2	Jane Smith

Orders:

OrderId	CustomerId	Quantity
1	1	5
2	1	3
3	2	2

The query would return the following result:

CustomerId	CustomerName	TotalQuantity
1	John Doe	8
2	Jane Smith	2

Note:

• CROSS APPLY is similar to OUTER APPLY, but OUTER APPLY returns a row even if the subquery doesn't return any results.
• In this specific example, you could achieve the same result using a JOIN with a GROUP BY clause. However, CROSS APPLY can be more flexible and efficient in certain scenarios, especially when dealing with complex subqueries.