Using OUTER APPLY and CROSS APPLY Effectively

So, you’re diving into SQL? That’s awesome! When it comes to using OUTER APPLY and CROSS APPLY, they can indeed be super helpful depending on your needs, especially in a library database scenario like the one you mentioned.

OUTER APPLY Example

Imagine you have these tables:

• Authors (AuthorID, Name)
• Books (BookID, AuthorID, Title, PublishDate)

You want to list all authors along with their latest book and the count of books they’ve authored. With OUTER APPLY, you can do this, ensuring that even if an author hasn’t published any books, they still show up in your results.

        
        SELECT a.Name, 
               latestBook.Title AS LatestBook,
               COUNT(b.BookID) AS BookCount
        FROM Authors a
        OUTER APPLY (
            SELECT TOP 1 Title
            FROM Books b
            WHERE b.AuthorID = a.AuthorID
            ORDER BY b.PublishDate DESC
        ) latestBook
        LEFT JOIN Books b ON b.AuthorID = a.AuthorID
        GROUP BY a.Name, latestBook.Title;
        
        

CROSS APPLY Example

Now for CROSS APPLY! If you only want to include authors who have published books, CROSS APPLY is perfect for that since it filters out those without any books. Let’s adjust the same example:

        
        SELECT a.Name, 
               latestBook.Title AS LatestBook, 
               COUNT(b.BookID) AS BookCount
        FROM Authors a
        CROSS APPLY (
            SELECT TOP 1 Title
            FROM Books b
            WHERE b.AuthorID = a.AuthorID
            ORDER BY b.PublishDate DESC
        ) latestBook
        JOIN Books b ON b.AuthorID = a.AuthorID
        GROUP BY a.Name, latestBook.Title;
        
        

When to Use Which

So, to sum it up:

• Use OUTER APPLY when you want all rows from the left table (e.g., all authors) even if there’s no matching row in the right table (books).
• Use CROSS APPLY when you only want rows where there are matching entries (authors with books).

Real-World Scenarios

In practice, you might find OUTER APPLY handy in reporting scenarios, where you want to include users even if they haven’t made any transactions. On the other hand, CROSS APPLY can be great for scenarios like filtering products for customers who have made purchases.

Hope this helps clear things up, and good luck with your SQL journey!