#086 How to return order details along with order items in Azure SQL

This T-SQL code performs a query using the CROSS APPLY operation, which is used to apply a table expression (or a table function) to each resulting row from the table to the left of the CROSS APPLY expression. The query returns details of items associated with the orders, using CROSS APPLY to relate the Orders and OrderItems tables.

Let's explain each part of the code:

SELECT Orders.OrderID, Orders.OrderDate, Details.Item
FROM Orders
CROSS APPLY (
    SELECT Item
    FROM ItemsOrder
    WHERE Orders.OrderID = OrderItems.OrderID
) Details;

1. Orders.OrderID, Orders.OrderDate, Details.Item

   :
   • SELECT: Indicates that the next part of the code will specify which columns will be returned in the query.
   • Orders.OrderID, Orders.OrderDate: These are the columns in the Orders table that will be included in the results.
   • Details.Item: This is the Item column of the table resulting from the CROSS APPLY operation, which contains details associated with the orders.

2. FROM:

   • Indicates the table from which the data will be selected.
   • Orders: This is the name of the main table from which the data will be extracted.

3. CROSS APPLY (...) :

   • CROSS APPLY: This is used to apply a table expression to each resulting row of the table on the left.

   • (SELECT Item FROM OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS Details: The table expression, which is a subquery, returns the item details associated with each order. CROSS APPLY ensures that the subquery is applied to each row of the Orders table.

   • THE DETAILS: This is the alias given to the table resulting from the CROSS APPLY expression.

code returns the IDs of the orders, their dates, and the items associated with each order. Using CROSS APPLY is useful when you need to combine a parent table with a related table by using a subquery. In this case, the subquery returns details of items related to each order.