My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today. I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper. [cc lang=”sql”] SELECT SalesOrderID = soh.SalesOrderID ,OrderDate = soh.OrderDate ,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) FROM AdventureWorks.Sales.SalesOrderHeader AS soh [/cc] It always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didn’t completely feel right either, but it was still just a bit cleaner: [cc lang=”sql”] SELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_price FROM AdventureWorks.Sales.SalesOrderHeader AS soh JOIN ( SELECT max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID ) sod ON sod.SalesOrderID = soh.SalesOrderID [/cc] What made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic … Continue reading CROSS APPLY Explained