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
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
	SELECT s2.productID ,AVG(s2.OrderQty) avgQTY
    FROM Sales.SalesOrderDetail s2
	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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *