Free SQL Help for Devs, DBAs, and the Curious

In this blog post, we will dive into the advanced usage of the CROSS APPLY operator for lookup joins in SQL. This operator, available in SQL Server 2016 and 2017, as well as Azure SQL, allows us to execute a function for each row in a query. While it can be used in a variety of scenarios, we will focus on its usage for joining tables.

Let's start with a simple example. Suppose we have a Customers table and an Orders table, and we want to fetch the last order for each customer. This can be achieved efficiently using the CROSS APPLY operator. Here's how we would do it in T-SQL:

SELECT C.CustomerName, O.OrderDate, O.OrderAmount
FROM Customers C
CROSS APPLY (
   SELECT TOP 1 *
   FROM Orders O
   WHERE O.CustomerID = C.CustomerID
   ORDER BY O.OrderDate DESC
) O

This code will execute the subquery for each row in the Customers table, and join the result with the corresponding row. The subquery fetches the most recent order for each customer, thus achieving a lookup join. Note that this kind of operation is not possible with a regular JOIN, because it does not allow for row-dependent operations.

However, it's important to note that not all database management systems support the CROSS APPLY operator. For instance, MySQL 5.7 does not have this functionality. In such cases, we can simulate a CROSS APPLY operation using a correlated subquery in the FROM clause. Here's how the previous example could be rewritten in MySQL:

SELECT C.CustomerName, O.OrderDate, O.OrderAmount
FROM Customers C
JOIN (SELECT CustomerID, MAX(OrderDate) MaxOrderDate
      FROM Orders
      GROUP BY CustomerID) MaxOrders MO
ON C.CustomerID = MO.CustomerID
JOIN Orders O
ON MO.CustomerID = O.CustomerID AND MO.MaxOrderDate = O.OrderDate

This code will first create a derived table with the most recent order date for each customer, and then join this table with the Customers and Orders tables to fetch the desired data. While not as elegant as the CROSS APPLY solution, it achieves the same result.

Similarly, DB2 11.1 does not support the CROSS APPLY operator, but it offers the LATERAL JOIN operator, which provides similar functionality. Here's how the example could be rewritten in DB2:

SELECT C.CustomerName, O.OrderDate, O.OrderAmount
FROM Customers C
JOIN LATERAL (
   SELECT *
   FROM Orders O
   WHERE O.CustomerID = C.CustomerID
   FETCH FIRST 1 ROWS ONLY
) O ON TRUE

In conclusion, the CROSS APPLY operator allows for powerful and efficient lookup joins in SQL. While not supported by all database systems, similar functionality can be achieved through other means. Remember to always explore the capabilities of the SQL version you are using, as it may offer efficient solutions to complex problems.