Correlated Subqueries with Comparison Operators

If you haven’t used a sub query yet, I’m surprised. They can be incredibly useful. They can also be a crutch…but like I say time and again, each tool has it’s use. I’d like to go into a little detail on sub queries today by talking about correlated sub queries.

Let’s say you wanted to find the product IDs and order quantities where the order quantities are less than the average order quantity for that product ID. This is something that would be fairly easy with a sub query.

USE AdventureWorks2008
GO
SELECT ProductID, OrderQty
FROM Sales.SalesOrderDetail s1
WHERE s1.OrderQty <
    (
		SELECT AVG(s2.orderQTY)
		FROM Sales.SalesOrderDetail s2
		WHERE s2.ProductID = s1.ProductID)

--you could also use a JOIN for the same effect

SELECT s1.ProductID, s1.OrderQty
FROM Sales.SalesOrderDetail s1
INNER JOIN (
	SELECT s2.productID ,AVG(s2.OrderQty) avgQTY
    FROM Sales.SalesOrderDetail s2
	GROUP BY
		s2.ProductID
)avgQty
	ON s1.ProductID = avgQty.ProductID
	AND s1.orderQty < avgQty.avgQTY

The outer query selects the rows of SalesOrderDetail (that is, of s1) one by one. The sub query calculates the average quantity for each sale being considered for selection in the outer query. For each possible value of s1, SQL Server evaluates the sub query and puts the record being considered in the results if the quantity is less than the calculated average.

I find the sub queries are usually used as a first attempt to get the answer you’re looking for. Many times, I move on to a JOIN, or on to a summary table to get the data. But, if you don’t learn this technique first, then you won’t be able to move on to those more advanced tools.

Let me know if you have any questions about sub queries, correlated or otherwise. I’m here to teach you everything you need to know about Microsoft SQL!