(SELECT MAX(OrdDet.UnitPrice)
FROM Northwind.dbo.[Order Details] AS OrdDet
WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord
This type of query is called a Correlated Subquery – you can see that there are two SELECT statements, the ‘main’ one (SELECT Ord.OrderID, Ord.OrderDate ) which selects the order ID and date from the orders table, and then the ‘sub’ query (in red), which selects the maximum unit price for each order.
The ‘sub’ query is actually ran once for each row that the main query returns, and this repeated access to the [order details] table can be fairly inefficient.
Books Online goes on to say that queries like the one above can usually be re-written as a straightforward join – the example below uses an INNER JOIN between the Orders and [Order Details] table in association with the MAX() function to produce exactly the same results, but more efficiently.
SELECT Ord.OrderID, Ord.OrderDate,
MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet
INNER JOIN
Northwind.dbo.Orders AS Ord
ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate
Although the same data is returned by both queries, Query Analyzer indicated that the second version took around 40% less SQL Server resources to run than the first, so no prizes for guessing which is the preferred option. However, in some cases we can use a third method to gain an even greater performance improvement.
Derived Tables
Using a derived table is in effect like using a temporary table, without the hassle of specifically creating and referring to one. I have re-coded the above BOL query to use derived tables in the example below:
SELECT Ord.OrderID, Ord.OrderDate, maxUnitPrice
FROM Northwind.dbo.Orders AS Ord INNER JOIN
(
SELECT orderID,
MAX(UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details]
GROUP BY OrderID
) AS OrdDet
ON ordDet.orderID = Ord.orderID
ORDER BY Ord.OrderID DESC, Ord.OrderDate, maxUnitPrice
The code in red causes the SQL Server optimizer to generate a notional (or derived) table called OrdDet for the duration of the query. The derived table notionally takes up much less space than the original [order details] table, because it contains only two columns and only one detail row for each order. Because of this, my ‘derived table’ version of the query should run even faster than the INNER JOIN version. When I checked the execution plan for the derived table version against that of the “join” version to see what sort of improvement I got, the results came out … exactly the same!