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;

Of course! Let's break down the function of CROSS APPLY in a clear, step-by-step way.

High-Level Analogy: A "For-Each" Loop

Think of CROSS APPLY as a "for-each loop" in SQL. For each single row from the table on the left (customers), it executes the subquery on the right.

Detailed Explanation of the Code

In your specific query, here is what CROSS APPLY is doing:

1. Outer Query Starts: The query engine first looks at the customers table. It takes the very first row. Let's imagine this row is for CustomerId = 1, CustomerName = 'John Smith'.

2. CROSS APPLY Executes: The CROSS APPLY operator now takes the CustomerId from that single row (1) and "passes it into" the subquery on the right.

3. Subquery Runs for that Customer: The subquery is executed as if it were written like this for John Smith:

   SELECT SUM(order.quantity) as TotalQuantity
   FROM orders order
   WHERE order.CustomerId = 1; -- The value from the current customer row
   

   Let's say this subquery calculates and returns a single row with a single column: TotalQuantity = 50.

4. Results are Combined: CROSS APPLY then combines the original row from customers with the result from the subquery. The final result for this first customer is:

   CustomerId	CustomerName	TotalQuantity
   1	'John Smith'	50

5. The Process Repeats: The engine then moves to the next row in the customers table (e.g., CustomerId = 2, CustomerName = 'Jane Doe') and repeats the entire process, running the subquery specifically for CustomerId = 2.

This continues until every row in the customers table has been processed.

Key Characteristics of CROSS APPLY

• Row-by-Row Evaluation: It evaluates the right-hand subquery for each row from the left-hand table expression.
• Correlation: The subquery on the right is typically a correlated subquery, meaning it depends on values from the outer query's current row (like customer.CustomerId in this example).
• Acts like an INNER JOIN: CROSS APPLY will only return rows from the left table (customers) if the right-hand subquery returns at least one row. If a customer has no orders, the subquery SUM(quantity) would return NULL, but since it's an aggregate without a GROUP BY, it still returns one row. However, if the subquery were SELECT * FROM orders... and a customer had no orders, that customer would be filtered out of the final result, just like with an INNER JOIN.

CROSS APPLY vs. INNER JOIN

For this specific example, you could achieve the same result with a more traditional INNER JOIN and GROUP BY:

SELECT
    customer.CustomerId,
    customer.CustomerName,
    SUM(ord.quantity) as TotalQuantity
FROM
    customers customer
INNER JOIN
    orders ord ON customer.CustomerId = ord.CustomerId
GROUP BY
    customer.CustomerId, customer.CustomerName;

So why use CROSS APPLY? It becomes much more powerful in situations where a JOIN is difficult or impossible, such as: * Calling a table-valued function (TVF) for each row. * Solving complex "Top-N-per-group" problems (e.g., "get the 3 most recent orders for each customer").

What about OUTER APPLY?

There is also an OUTER APPLY, which acts like a LEFT JOIN. If the subquery on the right returns no rows for a customer, OUTER APPLY will still include that customer in the final result, but with NULL values for the columns generated by the subquery (TotalQuantity would be NULL). This is useful for finding customers who have no orders.