6. CROSS APPLY

When we need specific data from table2 to be included in table1, a Cartesian join (CROSS APPLY) is used, which is why it takes too much time. Therefore, this method is not recommended. Instead, use a Common Table Expression (CTE) in MS SQL. In a large dataset of 50k to 100k records, the CROSS APPLY operation took 3-4 minutes to fetch the data. On the other hand, the Common Table Expression (CTE) took just under 1 second.

CROSS APPLY in SQL Server is used to join a table with a table-valued function or subquery. It works like an INNER JOIN but allows joining based on complex calculations or operations that can’t be directly expressed in a simple JOIN condition.

Key Points:

• It evaluates the subquery or table-valued function for each row in the left (outer) table.
• It is often used when you need to retrieve the top N rows, aggregate calculations, or a subset of data based on certain conditions.
• CROSS APPLY excludes rows in the left table that do not return any results from the subquery or table-valued function.

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

OrderID	CustomerID	OrderAmount
101	1	250
102	2	300
103	1	150
104	3	500

SELECT 
    c.CustomerID, 
    c.CustomerName, 
    o.OrderID, 
    o.OrderAmount
FROM 
    Customers c
CROSS APPLY
    (SELECT TOP 1 
        o.OrderID, 
        o.OrderAmount 
     FROM 
        Orders o 
     WHERE 
        o.CustomerID = c.CustomerID
     ORDER BY 
        o.OrderID DESC) o;

Output

CustomerID	CustomerName	OrderID	OrderAmount
1	Alice	103	150
2	Bob	102	300
3	Charlie	104	500

---

Faster approach using CTE:

WITH LatestOrders AS (
    SELECT 
        o.CustomerID,
        o.OrderID,
        o.OrderAmount,
        ROW_NUMBER() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderID DESC) AS rn
    FROM 
        Orders o
)
SELECT 
    c.CustomerID,
    c.CustomerName,
    lo.OrderID,
    lo.OrderAmount
FROM 
    Customers c
LEFT JOIN 
    LatestOrders lo
ON 
    c.CustomerID = lo.CustomerID
WHERE 
    lo.rn = 1;