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!