The Power of OUTER APPLY: A SQL Weapon to Expand Legacy Database

This article demonstrates using SQL Server's OUTER APPLY operator to enhance legacy views without complete rewrites. OUTER APPLY joins a table with a table-valued function or subquery per outer table row, returning outer rows even without inner matches. Key features include row-wise operation, legacy system integration, and simplified syntax for complex queries. A real-world scenario expands a view to include promotion data, using a new function to get the top two promotions per product. This function is incorporated into the view using OUTER APPLY. The updated view now displays promotion codes and usage counts alongside sales data. The example showcases how OUTER APPLY adds new functionality to the existing view, improving data analysis. The conclusion highlights OUTER APPLY's value in modernizing legacy databases, increasing efficiency, and improving maintainability. It's compared to CROSS APPLY, with OUTER APPLY acting like a left join and CROSS APPLY like an inner join. The article encourages developers to utilize OUTER/CROSS APPLY for enhancing legacy systems.